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
interested in electronic math to mechanical conversion
I fancy EXCEL & where ever possible I try to programe it. PLEASE help me with all the functions & their examples. Have done it for Numerology, Family genealogy, now Family trust fund am stuck with the “AND” function of using it. Please help me.83 years age. NO Website, wish I can develop one.
Where do you get stuck?
Where do you get stuck?
This is very useful, but I often don’t have access to the internet when I am working in Excel. Could you make this information in a PDF file, so that it would be accessible offline?
Please help. I have a column with a title.
If it has been checked how to I copy the title to another cell?
Thank you very much
Thank you sir I must so help ful.
=search(“9″,”99 9000”) = result is 1
How about another “9” position? What is formula?
Sir, 2nd Sept.2019.
Really you have shown very clearly and deeply all points.
I must thank you.
Hoping to receive more articles in future too.
Kanhaiyalal Newaskar.
Thank you for your generosity.
NICE, REALLY HELPFUL
Thank u
Thank you for working hard to help others. Your generous effort will be put into good use.
Thank you for your hard work for helping out us…
Use RANK function
This is amazing and very generous of you…many thanks…
You’re Welcome 🙂
This is very useful, but I often don’t have access to the internet when I am working in Excel. Could you make this information in a PDF file, so that it would be accessible offline?
Lavoro ottimo e ++ utile. Grazie e complimenti
nice information
we are here because you and your work is awesome. 😉
Keep on sharing. This help us a lot. Its a time save for us and no need to search each functions from net cause its already here.
Hi, I find your Excel tips very useful. It helps me a lot. Tips likes this give me a lesser time to search for the uses and functions of each formulas. Thanks a lot.
Very useful information..thank you sir
Sir, very useful information,notes and formula you have given.User like me would appreciate the work.
Once again thanks.
Thank you very much Mr. Punit Sir. I am computer faculty and recently I have asked to teach Excel functions and I find the complete list here. Could you possibly provide us the PDF format of all the functions with examples to use. Thank you.
Thanks a lot for sharing your knowledge with us with clear explanation. Your blog is my guru. All your excel tips are being very useful for my work. Thanks again.
GDay Punit
Needed a refresher, thanks for the info.
First of All.. Thank you for sharing the above info.. I fully appreciate this gesture..
A honest effort with intention of educating…. Really Super …………..
Hello Punit,
I am very thankful to you as you have start this learning portal for all. And thanks for giving ur stuff from this web portal i will be able to use excel smartly and efficiently. I learn so far with this really Excellent work ur doing
Thanks a lot again.
Thank you 🙂
Dear Puneet thanks, i want learn Macro in detail, can you help me
Thanks
Nitin
Please check each category. some files are not there or linked wrong
Dear Puneet,
Excellent work. Many files are not working or the link is wrong.
Thanks Rashid
Dear Puneet,
Excellent work. Many files are working or the link is wrong.
Thanks Rashid
Hi Puneet,
I want to learn macro VBA step by step please help me.. you are doing really great job
Thanks a lot.
Thank you so much puneet. It is very nice tutoral.
Its vry usefull. Nice info.
Its vry usefull
Thank you so much Puneet Gogia and nice to see the whole functions list in online,it is helpful to me.I’m really appreciate to you… i want to learn VBA pls need you guideline (confusion in coding)
Hi, Dear Punit,
I am very thankful to you as you have start this learning portal for all. In my office people are jealous they don’t give me chance to learn excel and no one guide me how to use excel smartly even though many of them are working smartly in excel. But from your this web portal i will be able to use excel smartly and efficiently. Thanks a lot again.
(Maraming salamat po sayo Puneet..) Thank you so much Puneet. It’s a great help for me to learn more from you
You’re Welcome.
Thank you so much
It will be better if you put more information about Excel macros. Thank you for your contributions in order to make people’s working lives easier. Keep up the good work.
my self deepak,
thanks for this, good job
kindly provide advance vba/excle programes
Great Puneet.. You made wonderful work to make everyone expert in Excel.
I want to have an example as well after explaining
after long time i find such amazing summary about excel function …thanxs alot sir
Gogia sir, plz suggest solution, if any
Sir it was done manually just to let you explain my actual need, please suggest function, if you can help in this regard.
https://uploads.disquscdn.com/images/0702942c4aa7355dffa9637ed2508ef37a5666306d6e3db4491e61dd4f600f9d.jpg there are 25 row in a column in which numbers are stored in-sequential like 0, 32,1,5,15,20,101,0,15,0,2,3,0………….. one figure in every one row, I need to allocate one serial number for all the duplicates that too in ascending order of the stored in-sequential figure, I want to apply sorting smaller to larger
You have already sorted in above snapshot?
please give formulas and function in the pdf formet
Thanks Puneet for your support
Very useful tips like camera option, Picture graph thanks a lot
Thanks a lot for providing a helpful resources. You did a excellent job. Thanks again…
Are these available in a PDF or Word File? This is a good reference, but it’s hard to remember to come back here for the information. Having them as something you can download would be really helpful.
How to get data of various sheets on Summarry sheet of the inventory register.
Thank you for the valuable information…I would like to know one specific thing.
Once we use the transpose, only values are copied. If i want to retain the values with formula in transpose function, is it possible. This is very important to keep scientific way to prove that the transpose operation done is correct. In data analytics big excel sheets are handled and a small mistake can lead to big problems .. Please advise me ..
How Can I creat a daly sale report in excel
please help me
Puneet, you are simply amazing.
Thanks It is very useful to know which formula where to use. Thanks Puneet Bro
Hi,
Please provide the link for Inv. Management System
very nice useful puneet how i download this excel function book
How can I create a timezone convertor??
Thanks punnet for providing such a good platform to learn excel
MS has already created a summary lists of all functions with links to brief descriptions.
functions (alphabetical) 2016-2007
https://support.office.com/en-us/article/Excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188
Functions (by category) – 2016 – 2007
https://support.office.com/en-us/article/Excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb
with transpose function can we reverse the data set; like AAA, BBB, CCC transpose to CCC,BBB,AAA. Thanks
Hi Puneet I think it is great if you combine these formulas in one store in PDF it is really awesome
I’d love it.but it will be nice if you combine it in a one PDF file 🙂
HI, is there any way to work out how long there is between 2 times? ie. 09:42 and 10:01 (that is, I want it to show 19 minutes)
Try,
=TEXT(10:01-09:42,”HH:MM:SS”)
SUBTOTAL – which I find is a powerful function, but often overlooked – particularly with the different function numbers that formula uses.
Thanks Dan.
GETPIVOTDATA, I haven’t nailed that one yet…dan.kordelski@gmaildotcom
OFFSET and exact use of Array with detailed example.
Thanks Mohammed Mubin Sayyed
Punnet thanks, May I suggest this, an average subscriber fairly uses excel and functions but in isolation. Every one knows match, offset etc what most need is the combination of these functions to generate more potent practical problem solving forces is the next step I will like you to do after these functions in isolation.
Yeah Iyek. I am trying to give two different examples. one is solo & other is combined with some other function.
Great! Puneet. I am looking forward to this.
OFFSET/MATCH AND ROW/COLUMN
Thanks Iyke.
Index or match
Thanks Angel
I would love others to learn these functions “Indirect, Offset, Match, Index, Vlookup, Mid, Find, Replace, Substitute, Sumif, Sumifs, SumProduct mainly and of course array functions as well.
I would really like to have a better understanding of the Indirect with Row and/or Columns function. Could we include these please.
Thanks Srinivasan
Offset plays a vital role
Hi Puneet, I saw someone at work using both the INDEX and MATCH functions. I see that someone has already requested MATCH so if possible, I would like to see INDEX added to the list. Thank you!
Alright Diana. Thanks for your reply.
Hello again, Puneet (and congratulations on your 1st 1,000 subscribers)! I would like to see the IFERROR function covered. I am just learning about it, so all lessons are greatly appreciated. Thank you for asking!
Thanks Brenda. I will for sure.
You’ll get your E-book on 30 Apr
i would like to Get a good description of the match function and/or the choose function. Thanks Torstein (sejohnse@yahoo.no)
Hey Torstein,
You will get the both.
Thanks for words.
You’ll get your E-book on 30 Apr
Hi, Can you add the sumproduct function. I want to know how you use it to bring back sums based on 3 or conditions. Thanks eannablack@gmail.com
Hello Eanna,
Already Added.
Thanks for your comment. You’ll get your E-book on 30 Apr
very helpful, i really appreciate. keep it up Bro
Nice to see the whole functions list..would appreciate if you let us know the complete descriptions of all functions.
Great to have such a summary available
Thanks Inet