basic math formulas

• Add : =C3+C4
• Subtract : =C3-C4
• Multiply : =C3*C4
• Divide : =C3/C4

can use a range

        =SUM(D3:D6)
    

to use AutoSum to total a column's values
can also use ALT+Equals
can use multiple args with SUM function

    =SUM(D38:D41,H:H)
    

can use AutoSum to calculate average
can also calculate average using

    =AVERAGE(G3:G6)
    

calculate median value using

    =MEDIAN(G3:G6)
    

function gives value which appears most often in the data

    =MODE(G3:G6)
    

can use AutoSum or formulas

    =MIN(D3:D6)

    =MAX(D3:D6)
    

get the current date using

    =TODAY()
    

add and subtract dates

    =D7+D6

    =D7-D6
    

get current time using

    =NOW()
    

add hours between times

	1	2
A	Status	Time
B	Time In	8 AM
C	Lunch Out	12 PM
D	Lunch In	1 PM
E	Time Out	5 PM
F	Total Hours

=((out time - in time) - (lunch in - lunch out)) * 24

        =((E2-B2)-(D2-C2))*24
    

the 24 converts fractional portion of the day into hours
need to format the cell as a number using

        Home > Format > Cells (CTRL+1) > Number & Number > 2 decimals
    

use '&' to join last name and first name cell values into a third cell

    =A1&", "&A2
    

to get full anme

    =A1&" "&A2
    

text and date

    C28&" "&TEXT(D28,"MM/DD/YYYY")
    

text and time

    C29&" "&TEXT(D29,"HH:MM AM/PM")
    

    =IF(C9="Apple",True,FALSE)
    

    =IF(C12<100,"Less than one hundred","Greater than or equal to one hundred")
    

SalesTax is a one cell named range

    =IF(E33="Yes","F31*SalesTax,0)
    

    =VLOOKUP(<<what to look for>,<where to look>,<how many columns to the right>,<exact or approximate match>)

    =VLOOKUP(A1,B:C,2,FALSE)
    

VLOOKUP returns #N/A on error
to hide error if first arg is blank

    =IF(C43="","",VLOOKUP(C43,C37:D41,2,FALSE)
    

hide error if first arg might not exist

    IFERROR(VLOOKUP(C43,C37:D41,2,FALSE),"")
    

if the code below the function looks for the value of C17 in the range C3:C14
if the value matches the corresponding value in column is is added to the sum

    =SUMIF(<range to sum>,<value to search for>,<for each match found what range to find the value to add to the sum >)

    =SUMIF(C3:C14,C17,D3:D4)
    

SUMIF with a value arg

    =SUMIF(D118:D122,">50")
    

the code below searches two different ranges in order usng different criteria for each range

    =SUMIFS(<range to sum>,<first range to look for matches>,<criteria for first match>,<second range to look for matches>,<criteria for second match>)
    

COUNTIF and COUNTIFS functions only take a range and criteria
count is incremented when a cell in the range matches the criteria
similar rpototypes to SUMIF and SUMIFS

    =COUNTIF(<range to search>,<value to search for>)

    =COUNTIFS(<first range to look for matches>,<criteria for first match>,<second range to look for matches>,<criteria for second match>)
    

AVERAGEIF and AVERAGEIFS

    =AVERAGEIF(<range to search>,<value to search for>,<for each match found what range to find the value to be added for averaging>)

    =AVERAGEIFS(<range to search>,<first range to look for matches>,<criteria for first match>,<second range to look for matches>,<criteria for second match>)
    

MAXIFS

    =MAXIFS(<range to search>,<first range to look for matches>,<criteria for first match>,<second range to look for matches>,<criteria for second match>)
    

MINIFS

    =MINIFS(<range to search>,<first range to look for matches>,<criteria for first match>,<second range to look for matches>,<criteria for second match>)
    

function wizard is opened by clicking the Insert FUnction button on the Formulas tab
selecting a cell with a formula and clicking on the buton will bring up a dialog which explains the function and its arguments
otherwise a dialog will appear which will let you select a function and then guides the process of adding args

to determine what is causing a formula error Formulas > ErrorChecking