Excel is typically one of the go to tool for business professionals when it comes to analyzing data and visualizing it. Conveniently, it supports hundreds of formulas, some of which are daily drivers for most people, and others, you may not even heard of. We’ve compiled the most common formulas used in data analysis tasks and created this guide to give you examples as how you can use them in different scenarios.
xIFS Function
This function can be used to find a value that meets certain conditions, within a complex data table. It has a certain advantage over the classic xIF functions like SUMIF, COUNTIF, and the others. xIF functions answer the need for comparing against multiple conditions and this feature make them just as popular as the VLOOKUP function.
xIFS function are as follows,
- SUMIFS (2007 and above)
- COUNTIFS (2007 and above)
- AVERAGEIFS (2007 and above)
- MAXIFS (2016 and above)
- MINIFS (2016 and above)
Syntax
All xIFS functions use the same syntax except for COUNTIFS. While COUNTIF doesn’t need any additional argument for doing a count operation, while the others use a range. Below is a syntax example for SUMIFS and COUNTIFS,
- SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Example
Let’s take an example. Assume that we have a dataset that contains employee information and we want to take a look at a specific scenario. What is the sum of Base Salary of all employees from a given department and state, and were paid overtime? A very specific question with several conditions. Let’s make this question a bit more specific,
What is the sum of Base Salary of employees in the state of Florida, who are in the Marketing Department and were paid overtime?
In our sample data the columns we’re going to be working with are States, BaseSalary, Overtime, and Department.
So the formula becomes,
=SUMIFS(BaseSalary,States,"FL",Departments,"Marketing",Overtime,">0").
VLOOKUP
VLOOKUP is one of the most famous functions of Excel as it can match data from a table with an input (lookup) value. If you want to search a value in a data table and want a single value result the easiest method is using a VLOOKUP function.
Syntax
Enter what you are looking for, where you are looking, the column number of the information you are looking for, and whether you want Excel to do an approximation or precise search.
VLOOKUP(Lookup_value, table_array, col_index_num, [range_lookup])
Example
Let’s again take a hypothetical question and ask an easy question.
Which department is one of our employees, HALEY BATES, is in?
Formularized version of this question would be,
=VLOOKUP("HALEY BATES",A1:C6,3,FALSE) for table at below.
INDEX+MATCH
VLOOKUP is fairly easy to use and can answer several problems, IF the table is designed well for VLOOKUP. VLOOKUP can only return values from columns which are to the right of your search column. For example, you can’t search for a value in column B and return a value from column A.
When used in combination, the INDEX and MATCH functions can overcome this limitation. This way, we can search and call data anywhere in data tables.
INDEX function pinpoints the data reference, whereas the MATCH function searches for a value in a single dimension array (or range), and returns the coordinates as a number. Numbers from MATCH functions essentially become the coordinates in the INDEX function!
Syntax
=INDEX(table you are looking, row coordinates, column coordinates)
=MATCH(what you want to look up, where you are looking, match type)
Combined: INDEX(table you are looking, MATCH(what you want to look up, where you are looking, match type), MATCH(what you want to look up, where you are looking, match type))
Note: If either the row or column coordinate has a static value, you can replace that value with one of the MATCH functions which is redundant in this case.
Example
Let’s again try searching for the department HALEY BATES is in. This time, let the name information be in the right-most column.
The combined formula becomes,
=INDEX(A2:C6,MATCH("HALEY BATES",C2:C6,0),MATCH("Department",A1:C1,0))
IF
The IF function can be very handy as it returns a value as a result of a logical text. Data models can be often simplified substantially using IF statements.
Syntax
IF(logical_test, value_if_true, [value_if_false])
Example
Let’s say we want to see a Yes/No breakdown for which employees are getting paid for overtime.
The formula will be,
=IF(I2>0,"Yes","No")
Basic Operators
Obvious, but easy to pass elements in formulas are operators and expressions. Knowing your way around them can come in useful when creating formulas.
| Arithmetic operator | Meaning | Example |
| + (plus sign) | Addition | 3+3 |
| – (minus sign) | Subtraction | 3–1 |
| Negation | –1 | |
| * (asterisk) | Multiplication | 3*3 |
| / (forward slash) | Division | 3/3 |
| % (percent sign) | Percent | 20% |
| ^ (caret) | Exponentiation | 3^2 |
| Comparison operator | Meaning | Example |
| = (equal sign) | Equal to | A1=B1 |
| > (greater than sign) | Greater than | A1>B1 |
| < (less than sign) | Less than | A1<B1 |
| >= (greater than or equal to sign) | Greater than or equal to | A1>=B1 |
| <= (less than or equal to sign) | Less than or equal to | A1<=B1 |
| <> (not equal to sign) | Not equal to | A1<>B1 |
| Text operator | Meaning | Example |
| & (ampersand) | Connects or concatenates two values to produce one continuous text string | ("North"&"wind") |
| Reference operator | Meaning | Example |
| : (colon) | Range operator which produces one reference for all the cells between two references, including the two references | B5:B15 |
| , (comma) | Union operator which combines multiple references into one reference | SUM(B5:B15,D5:D15) |
| (space) | Intersection operator which produces one reference for cells common to the two references | B7:D7 C6:C8 |
Text formulas
Text data can be cumbersome to manage in Excel. Fortunately there’s several useful functions to tidy up data. Let’s take a look at some common examples.
- LEFT, RIGHT and MID – These functions return a portion of a text. They can be very useful when combined with other functions, like those that return the length or position of characters (LEN, FIND or SEARCH).
| =LEFT(“This is the new way”,6) | This i |
| =RIGHT(“This is the new way”,3) | Way |
| =MID(“This is the new way”,10,5) | he ne |
- LEN – Returns the length of a text string.
| =LEN("This is the new way") | 19 |
- FIND and SEARCH – Both return the position of the desired character within a text string. Note that the FIND formula is case-sensitive, while SEARCH is not.
| =FIND("THE new","This is THE new way") | 9 |
| =SEARCH("the new","This is THE new way") | 9 |
- TRIM and CLEAN – These functions remove spaces and unwanted characters in a text. You can get rid of extra spaces or special characters in your data.
| =TRIM(" This is the new way ") | This is the new way |
| =CLEAN("This
is the new way") |
This is the new way |
- SUBSTITUTE and REPLACE – These functions replace a part of a string, with a different text. Even though SUBSTITUE replaces all instances of the searched part, you can select the position of where replacement action is being taken.
| =SUBSTITUTE("This is the new way","new","old") | This is the old way |
| =REPLACE("This is the new way",13,3,"old") | This is the old way |
- TEXT – This function helps you apply a certain formatting to a numeric value.
| ="This is "&TEXT(0.0469,"0.0%")&" the new way" | This is 4.7% the new way |
Excel is the all-in-one platform where you can collect, organize, and analyze data. We hope that this guide could help you get a better understanding of some commonly used formulas in Excel for analyzing data.
