The first step to using Excel is to understand the formulas. Excel functions and formulas are inserted into the cells.
If you are an aspiring business or data analyst, then you should have good knowledge of Excel and the formulas therein. There are several formulas that are specially designed for business analysis, and it is quite pertinent for you to have a good grasp of the formulas. Do you wish to have a taste of these formulas? Let's go! We will walk you through the top 10 Excel formulas for Business Analysts.
1. SUMIFS Formula
This formula will reduce the stress of massive filtering and manual selections. It is very efficient in collecting and summing up a well-defined set of data/values that meet certain criteria.
=SUMIFS(the values you want to sum up, criteria for column 1, criteria, criteria for column 2, criteria…)
=SUMIFS(sales, regions, "A", products, "B", customer types, "C", month, "M")
2. VLOOKUP Formula
This formula simplifies your efforts when searching for a particular value in a set of data. It unravels the number and also displays the corresponding value.
VLOOKUP formula lets you search for a value in a table and return a corresponding value. For example, you can ask the name of the customer with ID=C00023 or How much the product price for product code =p0089 and VLOOKUP would give you the answers.
Below is the syntax for VLOOKUP
=VLOOKUP( this value, your data table, column number, optional is your table sorted?)
=VLOOKUP("C003", customers' table, 3, false)
This syntax says that we Lookup customer ID C003 in the customers' table and return the value from the 3rd column.
We use "False" when we assume that the customers' table is unsorted.
3. INDEX & MATCH Formula
These two formulas are twins in nature. They assist in searching for designated values in any column, and they return the corresponding values without any limitations.
See below for their syntaxes:
=INDEX(the values, MATCH(the item you want to lookup, the column, sorting status of the column))
=INDEX(Student IDs, MATCH("Paul Peterson", Student names, 0) )
We use "0" because the column is not sorted.
4. Nesting Formulas
Business analysis involves complex formulas, and you can simplify it by nesting. By nesting, we mean inserting one formula in the other. Nesting is a must-learn formula for all business analysts because it helps us to simplify complex logic formulas. You can nest as many formulas as possible.
Give out a 10% hike to customers who visit our website at least 4 days per week; Give an 8% hike to customers who visit our website at least twice a week; for others, give a 3% hike.
The Nested formula for this is:
=IF(number of customers who visit our website in a week >=4, "10% hike", IF( number of times employee reads chandoo.org in a week >1, "8% hike", "3% hike"))
In the formula above, two IF formulas were nested together.
5. Text formulas
It is very pertinent for all aspiring analysts to know how to use text formulas in Excel. Text formulas are very efficient in cleaning and arranging tons of data. There are a number of text formulas; some are advanced while some are basic. For a start, you should be well vested with the following formulas:
LEFT, RIGHT & MID
Uses of these Text formulas are stated in our previous articles.
6. Basic Arithmetic Expressions
This is clearly basic mathematics operations, and I'm quite sure that you know about them. You actually need them too in excel. As a business analyst, you need to know the formulas for addition, subtraction, multiplication, division, power functions, etc.
Below is a typical example of excel Arithmetic functions:
=(((215+102)*(253+174)) > ((103-274)/(537-782)))^2
7. IF Formula
Businesses make decisions every day, and the IF function is handy for business analysts.
This is the syntax for IF function:
=IF(Condition for testing, True, False value)
For instance, your company may decide to give 15% bonuses to customers who buy from websites & 10% hike to others. Now, the Excel formula for this example is written below:
=IF(Customers who buy from our websites, "15% bonus", "10% bonus")
8. IS ERROR
Business analysis should be done devoid of errors and IS ERROR function assists in detecting errors in formulas. When you enter the formula, the result will be "true" if the cell is faulty, but will show "false" if the cell has no error.
The formula for IS ERROR function:
9. OFFSET formula
This formula solves problems involving ranges by taking references from a particular starting point. In this case, the height and width of the cells are given.
This is the OFFSET formula:
=OFFSET(starting point, rows to move, columns to move, height, width)
10. SUBTOTAL Formula
As the name implies, business analysts use the SUBTOTAL formula to deduce the subtotal of a given range of data in cells. This formula is versatile because it can carry out addition, find the average, and add a number of cells.
SUBTOTAL's formula is stated below:
=SUBTOTAL(Type of Total, Range of Cells)
The ten formulas listed here are essential tools in business analysis. As a Business analyst, you will come across these formulas on a daily basis.