excel formulas

IF function

=IF(condition, valueiftrue, valueiffalse)

IFS function

=IFS(condition1,result1, condition2,result2, …)

SWITCH function

=SWITCH(expression,value1,result1,[default])

SUMIF example

=SUMIF(B:B,"Movie",C:C) → add sales where category is Movie

COUNTIF example

=COUNTIF(D:D,">900") → count transactions >900

AVERAGEIFS example

=AVERAGEIFS(D:D,B:B,"Movie",C:C,"January") → avg Movie sales in January

MAXIFS example

=MAXIFS(SalesRange,LocationRange,"Draper")

MINIFS example

=MINIFS(SalesRange,LocationRange,"Draper")

AND function

=AND(condition1,condition2) → TRUE if both are true

OR function

=OR(condition1,condition2) → TRUE if at least one is true

NOT function

=NOT(condition) → reverses TRUE/FALSE

Nested grading formula

=IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",A2<70,"Fail")

VLOOKUP

=VLOOKUP(lookupvalue,tablearray,col_index,FALSE)

HLOOKUP

=HLOOKUP(lookupvalue,tablearray,row_index,FALSE)

MATCH

=MATCH(lookupvalue,lookuparray,[match_type])

INDEX

=INDEX(array,rownum,[colnum])

NOW function

=NOW() → returns date + time

TODAY function

=TODAY() → returns current date

WEEKDAY function

=WEEKDAY(serialnumber,[returntype])

WORKDAY function

=WORKDAY(start_date,days,[holidays])

NPER function

=NPER(rate,pmt,pv,[fv],[type])

PMT function example

=PMT(6%/12,60,-20000) → monthly payment for $20k loan, 6%, 60 months

Evaluate Formula

Tool that shows step-by-step how Excel calculates

Trace Dependents

Tool that shows which cells rely on the selected cell

Watch Window

Tool to monitor a cell’s value as data changes

Error Checking

Tool to validate formulas in a worksheet

Macro naming rule

Macro names cannot have spaces or start with a number