Data analysis can be a very arduous task if you do it manually. Nowadays, businesses, employees, and individuals depend on Excel. For their financial data analysis and computation. Of course, they all want to get their work done in a quick and efficient manner. You can only harness the deep-rooted benefits of Excel when you know how to use the advanced functions. In this post, we shall throw more light on the top 10 advanced Excel functions that you must learn.
1. VLOOKUP
VLOOKUP is a critical function, as you can use it to look up for a piece of particular information within a collection of data. You can copy the target information and replicate it on a new table. This function can be found in the drop-down list of all functions.
You can also use the insert option tab to enter the function by entering "VLOOKUP."
2. Sum Function
This function is useful in the addition of numbers within a given column. You can easily find the sum function button on the Home tab. Just select the section of the column and click on the sum function.
=SUM(column)
3. MAX MIN function
Every set of data contains maximum and minimum values. The MAX MIN function is efficient in selecting the highest and the least values from any selected data set. The highest number in the set will show up when you open the function tab to type and enter 'MAX.' As for the minimum value, enter 'MIN,' and the least value in the data set will show up.
4. IF Function
You can use the IF Function to check whether the information about a given set of data is correct. The function is entered in the variable box; then, from the result, you will know whether it meets the condition or not (True or false). Below is the formula for the IF function;
IF(Criteria,True,False value)
5. SUMIF Function
If you wish to lookup for certain criteria in a particular set of data, then you can use the SUMIF function to check which of the data aligns with your criteria. If they align, then you can add them up. This function has three tabs. The range tab is for the section of data you want to check. The criteria tab contains the cell, while the sum range tab cumulate data that aligns with your criteria.
6. COUNTIF Function
Formula: =COUNTIF(D5:D12,">=21″)
If you wish to lookup for certain criteria in a particular set of data, then you can use the COUNTIF function to check which of the data aligns with your criteria. If it does, then you count. Suppose you want to count the number of people whose shoe size is above 21, then you can use this advanced function as shown in the diagram below:
7. AND Function
This is one of the popular logic functions in Excel. You can use it to check whether certain specified conditions are met or not. After you enter the function, it will show "TRUE" if the conditions are met and "FALSE" if the conditions are not met.
Here is the AND function:
AND(logical1, [logical2], …)
Below is an example of AND function:
=AND(A2>=10, B2<5)
The result will be "TRUE" if cell A2 contains a number which is more than or the same as 10, and if cell B2 contains a number lower than 5. Otherwise, the result will be "FALSE."
8. OR function
This is a logical function you can use for comparing two values or two statements.
After you enter the function, it will show "TRUE" if one or any of the conditions are met and "FALSE" if non of the conditions are met.
This is the syntax for the OR Function:
OR(logical1, [logical2], …)
Below is an example of OR function:
=OR(A2>=10, B2<5)
The result will be "TRUE" if cell A2 contains a number which is more than or the same as 10, or if cell B2 contains a number which is lower than 5. It will return true if any of the conditions are met but, the result will be "FALSE" if all the conditions are not met.
9. XOR Function
XOR function is otherwise called "Exclusive OR Function." Programmers mostly use it in logical analysis. Below is the formula:
XOR(logical1, [logical2],…)
It is compulsory for you to include the "Logical 1" while "Logical 2" can be used as an option. More than 254 conditions, references, values, arrays, etc., can be tested using this function. For a basic XOR formula:
It is TRUE if Logical 1 or Logical2 are TRUE
It is FALSE if Logical 1 and Logical 2 are either TRUE or FALSE.
Now, consider the examples below:
=XOR(1>0, 2<1) is TRUE because one of the logic statements is TRUE
=XOR(1<0, 2<1) is FALSE because two of the logic statements are FALSE
=XOR(1>0, 2>1) is FALSE. because two of the logic statements are TRUE
10. NOT function
The NOT function has one of the simplest syntaxes in Excel. It is purely a reverse function. It displays TRUE when the conditions are not met but displays FALSE when the conditions are met.
Here is the syntax:
NOT(logical)
Example:
This formula "=NOT(2*2=4)" displays FALSE as the result because the Logical value of the Argument is correct.
=NOT(2*2=10) displays TRUE as the result because the Logical value of the Argument is incorrect.
On the whole, these ten functions are very important for all Excel users. Take your time to study them, know the basics, and handle real-life tasks from there.