I am using Excel Macros since last few years and from that experience I have created the list of commonly used excel functions that we can use.
| Formula | Value | Description |
| =ABS(-11) | 11 | gets Absolute value |
| =ADDRESS(1,1) | $B$1 | gets the address of cell |
| =AGGREGATE(4,0,A1:A10) | 33 | Calculates aggregates |
| =BIN2DEC(101) | 5 | converst binary to decimal |
| =CELL("contents",A1) | 33 | gets the contents of given cell |
| =CHAR(65) | A | gets the character for ascii value |
| =CHOOSE(2,33,44) | 44 | selects nth value from the given set of values |
| =CONVERT(23,"m","ft") | 75.45931759 | Converts the number in different measurement units |
| =COUNT(A1,B3) | 1 | counts the number of cells having numbers in it |
| =COUNTA(A1:A7) | 7 | counts non empty cells |
| =COUNTIF(A1:A7,2) | 0 | counts the cells that meet the condition |
| =EXACT("sagar","sagar") | TRUE | checks if two string are similar or not |
| =FACT(5) | 120 | gets the factorial of the number |
| =FIND("ten","sachin tendulkar") | 8 | finds the position of given string in other string |
| =FIXED(19.434,2) | 19.43 | rounds the number |
| =FLOOR(45345.6756,1) | 45345 | gets the integer number |
| =CEILING(45345.676,1) | 45346 | gets the integer number |
| =NOW() | 25-10-2013 17:19 | gets current time |
| =DAY(now()) | 25 | gets current day |
| =GCD(6,8) | 2 | find GCD |
| =HLOOKUP(2,A1:A10,3) | #N/A | Horizontal Lookup |
| =VLOOKUP(2,A1:A10,3) | #N/A | Vertical Lookup |
| =HOUR(NOW()) | 17 | gets current hour |
| =HYPERLINK("http://www.google.com","goog") | goog | creates the hyperlink in the cell |
| =IF(12>10,"12 is big","12 not big") | 12 is big | executes conditional statements |
| =INT(323.55) | 323 | gets the integer part of the number |
| =ISBLANK(a55) | TRUE | checks if the given cell value is blank |
| =ISEVEN(22) | TRUE | checks if the given value is even |
| =ISNONTEXT(a55) | TRUE | checks if the givenvalue is non-text |
| =ISNUMBER(22.44) | TRUE | checks if the given value is number |
| =ISODD(34) | FALSE | checks if the given value is odd |
| =ISTEXT("dsdsd") | TRUE | checks if the givenvalue is a text |
| =LARGE(A1:A44,1) | 56 | gets the nth largest value from the given set |
| =LCM(6,8) | 24 | gets the Least Common Multiple |
| =LEFT("sachin tendulkar",6) | sachin | gets the n characters from the left side of the string |
| =LEN("sachin") | 6 | gets the length of the given value |
| =LOWER("Sagar Salunke") | sagar salunke | converts the value into lower case |
| =MATCH(33,A1:A77,0) | 1 | searches the value in given range |
| =MAX(A1:A44) | 56 | finds the max value from given range |
| =Min(A1:A44) | 3 | finds the min value from given range |
| =MONTH(now()) | 10 | gets the current month |
| =MOD(44,3) | 2 | gets the remainder of division |
| =MID("sachin tendulkar",7,33) | tendulkar | gets the n characters from the given string |
| =MINUTE(now()) | 19 | gets current minutes |
| =NETWORKDAYS("20-jan-2013",NOW()) | 200 | counts the weekdays from given dates |
| =OR(12>33,FALSE) | FALSE | checks the OR conditions |
| =POWER(2,2) | 4 | gets the power of given number |
| =PROPER("sagar salunke") | Sagar Salunke | Converst the first letter of word to upper case |
| =QUOTIENT(5,2) | 2 | gets the QUOTIENT |
| =RAND() | 0.988171935 | gets the random number betweem 0 and 1 |
| =RANDBETWEEN(1,100) | 35 | gets the random number between 1 and 100 |
| =REPLACE("sachin ten",8,3,"Anjali") | sachin Anjalien | replace the part of string by other string |
| =RIGHT("sagar salunke",8) | salunke | get n characters from the right side of the string |
| =ROUND(4545.67,1) | 4545.7 | rounds the number |
| =ROW(A1) | 3 | gets the row number of given cell |
| =SEARCH("ten","sachin tendulkar") | 8 | search one string in other |
| =SMALL(A1:A99,2) | 5 | gets the nth smallest number from the given set |
| =SQRT(4) | 2 | gets the square root of the number |
| =SUBSTITUTE("sagar salunke","sagar","ganesh") | ganesh salunke | replace the part of string by other string |
| =SUM(A1:A11) | 268 | gets the sum of given numbers |
| =SUMIF(A1:A10,33) | 231 | gets the sum of numbers if criteria is fulfilled |
| =TODAY() | 25-10-2013 | gets today's date |
| =TRIM(" sagar salunke ") | sagar salunke | removes the spaces from left and right side |
| =TYPE(22) | 1 | gets the data type of the parameter |
| =TRUNC(3434.56767) | 3434.56 | truncates the number |
| =UPPER("sagar salunke") | SAGAR SALUNKE | converts the letters to upper case |
| =WEEKDAY(NOW()) | 6 | gets the weekday from the given date |
| =YEAR(NOW()) | 2013 | gets the year from the given date |
excellent material
ReplyDelete