MS Excel Secrets - Frequently Used Excel Formulas

• 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 toolmaker if you are using excel 2003 or earlier.
• To count number of words in a cell, use =len(trim(text))-len(SUBSTITUTE(trim(text),” “,”")).Like-If you want to calculate the no. of words in cell a1, then use the formula:
• =len(a1)-len(substitute(a1," ",""))+1
• 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

MS Excel Secrets - POST's INDEX

Related Posts:

How to Convert PowerPoint to Flash Flipbook