1. IF Function
IF Function returns a value if the condition you specify is TRUE, else some other value. In simple words, the IF function can test a condition first and returns a value based on the result of that condition.
Syntax
IF(logical_test,value_if_true,value_if_false)
Arguments
- logical_test: The condition which you want to evaluate.
- value_if_true: The value which you want to get if that condition is TRUE.
- value_if_false: The value which you want to get if that condition is FALSE.
Notes
- The maximum number of nested conditions you can perform is 64.
- You can use comparison operators to evaluate a condition.
Example
In the below example, we have used a comparison operator to evaluate different conditions.
- We have used a specific text to get in the result if the condition met or not.
- You can also use TRUE and FALSE to get in result.
- If you skip specifying a value to get the result if the condition is TRUE, it will return zero.
- And if you skip specifying a value to get the result if the condition is FALSE, it will return zero.
In the below example, we have used the IF function to create a nesting formula.
We have specified a condition and if that condition is false then we have used another IF to evaluate another condition and perform a task and if that condition is FALSE we have used another IF.
In this way, we have used IF five times to create a nesting formula. You can use the same for 64 times for a nesting formula.
2. IFERROR Function
IFERROR function returns a specific value if an error occurs. In simple words, it can test value and if that value is an error, it returns the value you have specified.
Syntax
IFERROR(value, value_if_error)
Arguments
- value: The value you want to test for the error.
- value_if_error: The value which you want to get in return when an error occurs.
Notes
- IFERROR function is concerned with the occurrence of an error, not with the type of the error.
- If you skip specifying value or value_if_error, it will return 0 in the result.
- It can test #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?, and #NULL!.
- If you are evaluating an array it will return an array of results for each item specified.
Example
In the below example, we have used the IFERROR function to replace the #DIV/0! with some meaningful text.
IFERROR is only compatible with 2007 and earlier versions. To deal with this problem, you can use ISERROR.
3. TRUNC Function
TRUNC Function returns an integer after truncating the original number. In simple words, it removes the decimals from a number to a specific precision and then returns the integer part of the result.
Syntax
TRUNC(number, [num_digits])
Arguments
- number: The number you want to truncate.
- [num_digits]: A number to specify precision to truncate a number.
Notes
- If you skip specifying multiple it will return with an error.
- It rounds away from zero.
- If you have two multiples on the same distance it will return the multiple which is higher than the number, you are rounding.
Example
In the below example, we have used TRUNC to truncate data for removing time from the dates.
4. SUMIF Function
SUMIF Function returns the sum of the numbers which meet the condition you specify. In simple words, it only considers and calculates the sum of values that fulfill the condition.
Syntax
SUMIF(range, criteria, [sum_range])
Arguments
- range: A range of cells from which you want to check for criteria.
- criteria: A criteria which can be a number, text, expression, cell reference or a function.
- [sum_range]: A cell range that has the values you want to sum.
Notes
- If the sum_range is omitted, the cells in the range will be summed.
- Make sure to use double quotation marks to specify Text criteria or criteria that include math symbols, which must be enclosed in double quotation marks.
- The size of the criteria range and sum range should be of the same size.
Example
In the below example, we have specified A1:A9 as the criteria range and B1:B9 as the sum range and after that, we have specified the criteria in A12 which has the value C.
You can also insert criteria directly into the function. In the below example, we have used an asterisk wildcard to specify a criterion that has an alphabet “S”.
And, if you skip specifying the sum range it will give you the sum of the criteria range. But, that will be only possible if the criteria range has numeric values.
5. INDEX Function
The INDEX function returns a value from a list of values based on its index number. In simple words, INDEX returns a value from a list of values, and you need to specify that value’s position.
Syntax
INDEX has two different syntaxes. In the first, you can use an array form of an index to simply get a value from a list using its position.
INDEX(array, row_num, [column_num])
In the second, you can use a referral form that is less used in real life but you can use it if you have more than one range to get value from.
INDEX(reference, row_num, [column_num], [area_num])
Arguments
- array: A range of cells or an array constant.
- reference: A range of cells or multiple ranges.
- row_number: The number of the row from which you want to get the value.
- [col_number]: The number of the column from which you want to get the value.
- [area_number]: If you are referring to more than one range of cells (using reference syntax), specify a number to refer to a range from all those.
Notes
- When both the row_num and column_num arguments are specified, it will return the value in the cell at the intersection of both.
- If you specify row_num or column_num as 0 (zero), it will return the array of values for the entire column or row, respectively.
- When row_num and column_num are out the range, it will return an error #REF!.
- If area_number is greater than the number ranges you have specified then it will return #REF!.
Example 1 – Using ARRAY to Get Value from a List
In the below example, we have used the INDEX function to get the quantity of June month. In the list, Jun is in 6th position (6th row) that’s why I have specified 6 in row_number. INDEX has returned the value 1904 in the result.
And if you referring to a range with more than one column you have to specify the column number.
Example 2 – Using REFERENCE to Get Value from Multiple Lists
In the below example, instead of selecting all the range in one go, I have selected it as three different ranges. In the last argument, we have specified 2 in area_number which will define the range to use from these three different ranges.
Now in the second range, we are referring to the 5th row and 1st column. INDEX has returned the value 172 which in the 5th row in the 2nd range.
6. VLOOKUP Function
The VLOOKUP function lookups for a value in the first column of a table and returns the value from the same row of the matched value using the index number. In simple words, it performs a vertical lookup.
Syntax
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Arguments
- lookup_value: A value that you want to search in a column. You can refer to a cell that has the lookup value or you can directly enter that value into the function.
- table_array: A range of cells, a named range from which you want to look up the value.
- col_index_num: A number represents the column number from which you want to retrieve the value.
- range_lookup: Use false or 0 to make an exact match and true or 1 for an appropriate match. The default is True.
Notes
- If VLOOKUP cannot find the value you are looking for, it will return an #N/A.
- VLOOKUP is only able to give you the value which is on the right side of the lookup value. If you want to look upon the right side, you can use INDEX and MATCH for that.
- If you are using an exact match then it will only match the value which is first in the column.
- You can also use wildcard characters with VLOOKUP.
- You can use TRUE or 1 if you want an appropriate match and FALSE or 0 for an exact match.
- If you are using an appropriate match (True): It will return the next smallest value from the list if there is no exact match.
- If the value which you are looking for is smaller than the smallest value in the list, VLOOKUP will return #N/A.
- If there is an exact value exists which you are looking for, it will give you that exact value.
- Make sure you have sorted the list in ascending order.
Example
1. Using VLOOKUP for Categories
In the below example, we have a list of students with marks they have scored, and in the remarks column, we want to a grade according to their marks.
In the above marks list, we want to add remarks as per the below category range.
In this, we have two options to use.
FIRST is to create a nesting formula with IF which is a little bit time-consuming, and the SECOND option is to create a formula with VLOOKUP with an appropriate match. And, the formula will be:
=VLOOKUP(B2,$E$2:$G$5,3,TRUE)
How it works
I am using the “MIN MARKS” column to match the lookup value and I am getting value in return from the “Remarks” column.
I have already mentioned that when you use TRUE and there is no exact match lookup value then it will return the next smallest value from the lookup value. For example, when we are looking for a value 77 from the category table, 65 is the next smallest value after 77.
That is why we got “Good” in remarks.
2. Handling Errors in VLOOKUP Function
One of the most common problems which come when you are using VLOOKUP is that you’ll get #N/A whenever there is no match is found by it. But the solution to this problem is simple and easy. Let me show with an easy example.
In the below example, we have a list of names and their age and in cell E6, we are using the VLOOKUP function to look up a name from the list. Whenever I type a name that is not on the list I am getting #N/A.
But what I want here is to show a meaningful message instead of the error. The formula will be: =IFNA(VLOOKUP(D6,Sheet3!$A$1:$B$14,2,0),”Not Found”)
How it works: IFNA can test a value for #N/A and if there is an error you can specify a value instead of the error.
7. IFNA Function
IFNA function returns a specific value if an #N/A error occurs. Unlike IFERROR, it only evaluates the #N/A error and returns the value you specified.
Syntax
IFNA(value, value_if_na)
Arguments
- value: The value you want to test for #N/A error.
- value_if_na: The value you want to return if an error occurred.
Notes
- If you skip specifying any argument, IFNA will treat it as an empty string (“”).
- If a value is an array then it will return the result as an array.
- It will ignore all other errors #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?, and #NULL!.
Example
In VLOOKUP function, #N/A occurs when the lookup value is not in the lookup range and for that we have specified a meaningful message using IFNA.
Note: IFNA is introduced in Excel 2013 so it is not available in the previous versions.
8. RAND Function
The RAND function returns a random number ranging from 0 to 1. In simple words, you can generate a random number between 0 to 1 (it updates its value every time you make a change in the worksheet).
Syntax
RAND()
Arguments
- There is no argument to specify in RAND functions
Notes
- If you put zero in multiple it will return zero in the result.
- If you skip specifying multiple it will return with an error.
- It rounds away from zero.
- If you have two multiples on the same distance it will return the multiple which is higher than the number you are rounding.
Example
Apart from having numbers between 0 and 1, you can also use RAND for random numbers between two specific numbers. In the below example, I have used it to create a formula that generates a random number between 50 and 100.
When you enter this formula in a cell it returns a number between 100 and 50 by multiplying the values returned by the RAND with the equation we have used. To understand this formula we need to split it into three parts:
- First of all, when it detects the lowest number from the highest number you get the difference between both of them.
- Then secondly it multiplies that difference with the random number returned after the subtraction.
- And third, add that number with the lowest number left in the third part of the equation.
9. SUM Function
SUM function returns the sum of the values supplied. In simple words, with the SUM function, you can calculate the sum of a list of values (you can directly input value into the function or refer to a range of cells.
Syntax
SUM(number1,[number2],…)
Arguments
- number1: A number, a range of cells that contain numbers, or a single cell that contains a number.
- [number2]: A number, a range of cells that contain numbers, or a single cell that contains a number.
Notes
- It ignores text values.
Example
In the below example, you can insert numbers directly into the function by using commas between them.
You can also simply refer to a range for calculating the sum of the numbers and if there is a text, logical value, or empty cell it will ignore them.
If there is an error value in a cell that you are referring to, it will return #N/A in the result.
If you have numeric values that are formatted as the text it will ignore them. It’s recommended to convert them into numbers before using SUM.
10. OR Function
OR Function returns a Boolean value (TRUE or FALSE) after testing conditions you specify. In simple words, you can test multiple conditions with AND function and it returns TRUE if any of those (or all) conditions is TRUE and returns FALSE only if all those conditions are FALSE.
Syntax
OR(logical1, [logical2], …)
Arguments
- logical1: Condition which you want to verify.
- [logical2]: Additional Conditions you want to verify.
Notes
- Values will be ignored if the reference cell or array contained an empty cell or text.
- The result of conditions should be in logical value (TRUE or FALSE).
- It will return an error if there is no logical value is returned.
Example
In the below example, we have created a condition using IF function that if a student score 60 above marks in one of the both of the subjects the formula returns TRUE.
Now in the below example, we have used a number to get logical values in a formula. You can also perform the above condition in reverse order.
You can use TRUE and FALSE instead of numbers. OR function treats these logical values as numbers.
More Tutorials
Statistical Functions / Date Functions / String – Text Functions / Financial Functions