To get the first name of a person, use =left(name,find(” “,name)-1)
To calculate mortgage payments, use =PMT(interest-rate,number-of-payments,how-much-loan)
To get nth largest number in a range, use =large(range,n)…
To get nth smallest number in a range, use = small(range,n)…
To generate a random phone number, use =randbetween(1000000000,9999999999), needs analysis toolpak if you are using excel 2003 or earlier…
To count number of words in a cell, use =len(trim(text))-len(SUBSTITUTE(trim(text),” “,””))…
To count positive values in a range, use =countif(range,”>0?)…
To calculate weighted average, use SUMPRODUCT() function
To remove unnecessary spaces, use =trim(text)
To format a number as SSN using formulas, use =text(ssn-text,”000-00-0000?)…
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
To get name from initials from a name, use IF(), FIND(), LEN() and SUBSTITUTE() formulas…
To get proper fraction from a number (for eg 1/3 from 6/18), use =text(fraction, “?/?”)
To get partial matches in vlookup, use * operator like this: =vlookup(“abc*”,lookup_range,return_column)
To simulate averageif() in earlier versions of excel, use =sumif(range, criteria)/countif(range, criteria)
To debug your formulas, select the portions of formula and press F9 to see the result of that portion…
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.)
To quickly insert an in cell micro-chart, use REPT() function…
COUNT() only counts number of cells with numbers in them, if you want to count number of cells with anything in them, use COUNTA()
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
