20 Situations and How to Solve them using Excel Formulas

1. To get the first name of a person, use =left(name,find(” “,name)-1)
2. To calculate mortgage payments, use =PMT(interest-rate,number-of-payments,how-much-loan)
3. To get nth largest number in a range, use =large(range,n)… Get Full Tip
4. To get nth smallest number in a range, use = small(range,n)… Get Full Tip
5. To generate a random phone number, use =randbetween(1000000000,9999999999), needs analysis toolpak if you are using excel 2003 or earlier… Get Full Tip
6. To count number of words in a cell, use =len(trim(text))-len(SUBSTITUTE(trim(text),” “,”"))… Get Full Tip
7. To count positive values in a range, use =countif(range,”>0″)… Get Full Tip
8. To calculate weighted average, use SUMPRODUCT() function
9. To remove unnecessary spaces, use =trim(text)
10. To format a number as SSN using formulas, use =text(ssn-text,”000-00-0000″)… Get Full Tip
11. To find age of a person based on DOB, use =TEXT((NOW()-birth_date)&”",”yy “”years”" m “”months”" dd “”days”"”), output will be like 27 years 7 months 29 days
12. To get name from initials from a name, use IF(), FIND(), LEN() and SUBSTITUTE() formulas… Get Full Tip
13. To get proper fraction from a number (for eg 1/3 from 6/18), use =text(fraction, “?/?”)
14. To get partial matches in vlookup, use * operator like this: =vlookup(“abc*”,lookup_range,return_column)
15. To simulate averageif() in earlier versions of excel, use =sumif(range, criteria)/countif(range, criteria)
16. To debug your formulas, select the portions of formula and press F9 to see the result of that portion… Get Full Tip
17. To get the file extension from a file name, use =right(filename,3) (doesn’t work for files that have weird extensions like .docx, .htaccess etc.)
18. To quickly insert an in cell micro-chart, use REPT() function… Get Full Tip
19. COUNT() only counts number of cells with numbers in them, if you want to count number of cells with anything in them, use COUNTA()
20. Using named ranges in formulas saves you a lot of time. To define one, just select some cells, and go to menu > insert > named ranges > define