Pages

Saturday 7 September 2013

Excel Formulae to Solve the Situational Needs

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

No comments:

Post a Comment