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)
4. To get nth smallest number in a range, use = small(range,n)
5. To generate a random phone number, use =randbetween(1000000000,9999999999), needs analysis toolpak if you are using excel 2003 or earlier
6. To count number of words in a cell, use =len(trim(text))-len(SUBSTITUTE(trim(text),” “,”"))
7. To count positive values in a range, use =countif(range,”>0″)
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″)
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
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
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
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