excel formulas

SUM

Gives the sum of a range

=SUM(range), =SUM(cell:cell)

AVERAGE

gives the mean of a range

=AVERAGE(range)

STDEV

gives the standard deviation of a range

=STDEV(range)

MIN

gives minimum of a range

=MIN(range)

MAX

gives max of a range

=MAX(range)

COUNT

counts the number of cells in a range that contain a numerical entry

=COUNT(range)

COUNTA

counts number of cells in a range that contain a numerical entry or text string entry

=COUNTA(range)

COUNTBLANK

counts the number of blank cells in a range

=COUNTBLANK(range)

COUNTIF

Counts the number of cells in a range that (1) contain the specified value, or (2) satisfy a condition

=COUNTIF(range,10) gives number of cells in a range that have a value of 10

=COUNTIF(range,"Yes") give snumber of cells that contain the text string Yes

=COUNTIF(range,">=10")gives the number of cells in a range that are greater or equal to 10

=COUNTIF(range, C10) gives the number of cells in a range that contain the value in cell C10

COUNTIFS

counts the number of cells that satisfy more than one condition. It counts the number of cells in a range that satisfy all the conditions included in the formula. It will handle as many conditions as you want (up to 127)

=COUNTIFS(range1,10,range2,10)

=COUNTIFS(range1,">10", range1, <=20")

COUNTIFS can manage numerical and text values and inequalities in the same way that COUNTIF handles them

SUMIF

gives the sum in a range that (1) contain the specified value, or (2) satisfy a condition

=SUMIF(range,10) gives the sum of cells in a range that have the value 10

=SUMIF(range,">=10") gives the sum of cells in a range that are greater or equal to 10

=SUMIF(range,C10) gives the sum of the cells in a range that contain the value in c10

SUMIFS

gives the sum of values in the sum range that satisfy more than one condition. The second and third arguments give the first condition range and the first condition, respectively. The fourth and fifth arguments give the second condition range and second condition, respectively. and so on.

=SUMIFS(sumrange,conditionrange1,10,conditionrange2,10)

=SUMIFS(sumrange,conditionrange1,">10",conditionrange1,"<=20")

=SUMIFS(sumrange,conditionrange1,"=A*", conditionrange2,"Tom") gives the sum of the values in the sum range, if the values in condition range 1 satisfy condition 1 AND condition range 2 satisfy condition 2.

IF

conditional function. first argument states the condition, second contain the return value if the condition TRUE, and the third condition contains the return value if the condition is FALSE

=IF(condition,returnvalue_if_TRUE,returnvalue_if_FALSE)

=IF(cell>0,1,0) returns a value of 1 if cell value is 1:otherwise, it returns a 0.

=IF(cell="yes",1,0) returns a value of 1 if the cell ontains the string "yes"; otherwise, it returns a zero.

=IF(cell=1,"Yes","No")

AND

returns a value of TRUE if all the conditions listed are true. It return FALSE at least one of the conditions is not true.

=AND(condition1, condition2,...)

=AND(cell1>10,cell1<=20) returns TRUE if the value in cell1 is greater than 10 and less than 20;otherwise FALSE

=AND(cell1="yes", cell2="yes") returns TRUE if both cell1 and cell2 contain the text string yes.

OR

returns a value of TRUE if any the conditions listed are true. It returns FALSE if none of the conditions are true.

=OR(condition1, condition2,...)

=OR(cell1<10,cell1<=20) returns TRUE if the value in cell1 is less than 10 OR greater than or equal to 20; otherwise FALSE

=OR(cell1="yes",cell2="yes") returns TRUE if either cell1 or cell2 contains the string yes.

Nesting functions

-use one function as an argument of another function

-nesting level limits : formulas can contain several levels of nested functions. How many depends on the function but often its up to 20 or more

EX:

=IF(AND(cell1>10,cell1<20),1,0) returns a value of 1 if the value in cell1 is greater than 10 and less than or equal to 20:otherwise FALSE

INDEX

returns the value in an array located the row number in the array corresponding with rowno and the column number of the array corresponding with columno

=INDEX(array,rowno,columno)

=INDEX($C$2:$E$4,1,3) gives the value in the array $C$2:$E$4 that is row 1 column 3 of the array. This would be the value in cell E2

CONCATENATE

strings of text contained in more than one cell reference or specified in the formula. Note that any text characters included in the formula must be surrounded by double quotes

ex: suppose cell1 contains last names and cell2 contains first names; for example cell1=Doe, cell2=Jane

=CONCATENATE(cell2,"_",cell1) returns Jane_Doe

=CONCATENATE(cell2, " ",cell1) returns Jane Doe

=CONCATENATE(cell1",",cell2) returns: Doe, Jane

RIGHT or LEFT

return the specified number of characters at the right (left) end of a text string

=RIGHT(cell,2) returns the first 2 characters on the right of a text string

VALUE

can be used to convert text (if the characters are numerals) into numeric form so they can be sued in calculation

=VALUE(E9) from the above table replace 60 (understood as text) with 60 (understood as a number)

TYPE

reveals type of data in contents of a cell, i.e. whether it is classified as a number, text, logical value, error, and so on

ex:

if cell contains a number, type returns value of 1

text=2

logical value(true or false)=4

error value=16

IFERROR

returns as specified value if a cell contains an error code

=IFERROR(cell,value_if_error)

=IFERROR(cell,"") returns a blank if a cell contains an error

ISBLANK

returns a value of TRUE if a cell is blank; otherwise it returns FALSE

=ISBLANK(cell)

ISERROR

returns a value of TRUE if a cell contains an error code; otherwise it returns FALSE

NA()

returns the missing value code #N/A

=NA()

ISNA

returns a value of TRUE if the cell contains the #N/A code