Do you still remember being asked to write a formula in Excel and then being baffled not knowing which one is appropriate? Well, I still do. I remember how I dreaded vlookups and a loop of infested if statements trying to get results. Well, in the world of data analysis, functions play a vital role in extracting valuable insights from raw data. Functions enable analysts like me to perform various calculations, manipulations, and transformations on data sets. In this article, I reflect on how long I have come in my analyst journey. I will also explore four essential types of functions analysts frequently utilise. This includes (1) Number Functions, (2) Text Functions, (3) Date Functions, and (4) Boolean Functions. I learnt how to use most of these functions in Excel and I then made tweaks to achieve similar results when using other software applications. Understanding these functions will equip more analysts with the necessary tools to work with different types of data and derive meaningful conclusions.
Number Functions
Number Functions are essential for performing mathematical operations on numerical data. These functions are common mainly when manually calculating descriptive statistics. They enable analysts to conduct calculations, aggregations, and comparisons. Some commonly used Number Functions include: (1) SUM: Which adds up a range of numbers, (2) AVERAGE: Which calculates the average of a set of numbers, (3) MAX: Which returns the maximum value from a range of numbers, (4) MIN: Returns the minimum value from a range of numbers, (5) COUNT: Counts the number of cells in a range that contain numbers. Number Functions are particularly useful when analysing financial data, sales figures, or any other numeric data that requires computation or statistical analysis.
Text Functions
Text Functions were useful when I wanted to manipulate and analyse textual data. I often encountered situations where I need to extract specific information from text, combine different text values, or perform comparisons based on text conditions. I can recommend we start by using the following Text Functions: (1) CONCATENATE: Joins multiple text strings together, (2) LEN: Determines the length of a text string, (3) LEFT/RIGHT: Extracts a specific number of characters from the left or right side of a text string, (4) FIND: Finds the position of a specific character or text within a text string, (5) SUBSTITUTE: Replaces specific text within a text string with new text. Text Functions become handy and invaluable when working with data such as customer/employee names, branch details, product descriptions, or any other textual information. Using the above functions will allow us to focus on what matters the most. There are many instances where I had to use more than one Text Function in one formula.
Date Functions
Date Functions are designed to handle dates and perform various operations on them. This type works well when your data field/column has been formatted as Date. I used these functions when I needed to extract specific components from dates, calculate time differences, or group data based on dates. Some Date Functions I am fond of include (1) TODAY: Returns the current date, (2) DateAdd() Today() DateDiff() Month() IsDate() YEAR/MONTH/DAY: Extracts the year, month, or day from a given date, (3) DATEDIF: Calculates the difference between two dates, (4) EOMONTH: Returns the last day of the month for a given date, (5) WEEKDAY: Determines the day of the week for a given date. You can also feel free to explore DATEADD, DATEDIFF, or ISDATE to be ready for date-related analysis especially when analysing time series data, tracking trends, or performing any investigation that involves temporal information.
Boolean Functions
Boolean Functions are used to evaluate logical conditions and return either true or false values. I often use Boolean Functions to make comparisons, test conditions, or filter data based on specific criteria. They are what we normally get from management questions when analysis is required. I normally replaced some words in the questions with Boolean functions. My favourite Boolean Functions are (1) IF: Which performs a logical test and returns different values based on the result, (2) AND/OR: Which evaluates multiple conditions and returns true or false based on the logical outcome, (3) NOT: Inverts the logical value of a given condition, (4) COUNTIF: Counts the number of cells in a range that meet specific criteria, and (5) ISBLANK: which is ideal to check if a cell is empty or contains a value.
Boolean Functions are important when we want to compute values for decision-making, data filtering, and creating logical statements in data analysis. Boolean values describe whether a certain condition is true or false. There are four comparison operators I have used to compute Boolean values. These operators are: (1) equals “=”, (2) greater than “>”, (3) less than “<”, and (4) not equal to “¹”. Boolean values, whether “True” or “False”, are variables that could be used in Boolean Functions. An example could be IF ( LOGICAL_TEST , OUTCOME_IF_TRUE , OUTCOME_IF_FALSE ). The application of Logical Functions is often referred to as Boolean Logic
Conclusion
In the world of data analysis, functions play a vital role in extracting valuable insights from raw data. Functions used in analytical software applications like Excel enable analysts like me to perform various calculations, manipulations, and transformations on data sets. They are powerful tools to manipulate and extract insights from various types of data. For this reason, understanding (1) Number Functions, (2) Text Functions, (3) Date Functions, and (4) Boolean Functions allows us to perform complex calculations, transformations, and evaluations. By leveraging these functions effectively, we can derive valuable information, identify patterns, and make data-driven decisions.
