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

Related

MS Excel 3348115970792842473

Post a Comment

SPAMMING will not be Appreciated.

emo-but-icon

Hot in week

Recent

Comments

Blog Archive

Our Channel

Contact Us

Name

Email *

Message *

Follow by Email

item