SELECTED USEFUL EXCEL FORMULAS FORMULA DESCRIPTION SYNTAX Casing Functions PROPER change text string to proper casing =PROPER(text or cell reference) UPPER change text string to upper …… casing=UPPER(text or cell reference) LOWER change text string to lower casing=LOWER(text or cell reference) String Manipulation& concatenate text and cells together=A1 & “text” & A2 LEFT return N # of characters from the left=LEFT(text,num_chars) RIGHT return N # of characters from the right=RIGHT(text,num_chars) FIND find the first specified …

SELECTED USEFUL EXCEL FORMULAS FORMULA DESCRIPTION SYNTAX Casing Functions PROPER change text string to proper casing =PROPER(text or cell reference) UPPER change text string to upper casing =UPPER(text or cell reference) LOWER change text string to lower casing =LOWER(text or cell reference) String Manipulation & concatenate text and cells together =A1 & “text” & A2 LEFT return N # of characters from the left =LEFT(text,num_chars) RIGHT return N # of characters from the right =RIGHT(text,num_chars) FIND find the first specified character in a text string =FIND(find_text, within_text,start_num) INDIRECT returns reference specified by a text string =INDIRECT(ref_text) DATEVALUE converts a date to corresponding number =DATEVALUE(date_text) Lookup Functions VLOOKUP looks for a value in Nth column of a vertical array of data =VLOOKUP(lookup_value,table_array,col_index_num, range_lookup) HLOOKUP looks for a value in Nth row of a horizontal array of data =HLOOKUP(lookup_value,table_array,row_index_num, range_lookup) MATCH returns relative position of a value in an array =MATCH(lookup_value,lookup_array,match_type) Switches IF performs an action based on a logical test =IF(logical_test,value_if_true,value_if_false) CHOOSE chooses an action or value based on a number =CHOOSE(index_num,value1,value2, …) OFFSET returns a value in a cell offset from a reference cell =OFFSET(reference,rows,cols,height,width) Error Checking ISERROR checks if a value/formula is an error =ISERROR(value) ISNA checks if a value/formula is N/A =ISNA(value) ISBLANK checks if a cell is blank =ISBLANK(value) Mathematical Calculations MAX returns the largest value of an array of numbers =MAX(number1,number2,…) AVERAGE returns the average of an array of numbers =AVERAGE(number1,number2,…) MEDIAN returns the median of an array of numbers =MEDIAN(number1, number2,…) MIN returns the smallest value of an array of numbers =MIN(number1,number2,…)

Download SELECTED USEFUL EXCEL FORMULAS.Pdf