Formulas and Functions: References, Logic and Lookup
Reference behaviour is the main trap
Every copied formula question tests relative (A1), absolute ($A$1) and mixed (A$1, $A1) references. Press F4 to cycle them. Copying =B2*$F$1 down one row and right one column gives =C3*$F$1: the column changed, the anchor did not. Named ranges behave like absolute references and make formulas readable, but check the scope (workbook or sheet).
Logical functions
IF(test, value_if_true, value_if_false) returns text only if it is quoted: =IF(B2>=50,"Pass","Fail"). Nest IFs in the order that excludes conditions already handled, or use IFS, AND, OR and NOT to keep formulas readable. Remember operators: = comparison versus = assignment in plain text, <> for not equal, and & for concatenation.
Conditional aggregation
SUMIF(range, criteria, sum_range), COUNTIF, AVERAGEIF and their plural ...IFS versions add criteria. Criteria containing an operator must be in quotes: =SUMIF(A2:A10,">100",B2:B10). Note that COUNT counts numbers only, COUNTA counts non-empty cells, and COUNTBLANK counts empties - a frequent discrimination question.
Lookup
VLOOKUP(value, table, col_index, FALSE) needs the key in the first column and an exact-match flag of FALSE (or 0); omitting it gives an approximate match on unsorted data and wrong answers. INDEX with MATCH(...,0) avoids the left-column restriction and survives inserted columns. Wrap lookups in IFERROR to replace #N/A with a friendly message.
Recognise errors
#DIV/0! division by zero, #VALUE! wrong data type, #REF! deleted reference, #NAME? unrecognised name, #N/A no match, and a circular reference warning when a formula refers to its own cell.
Worked example
=IFERROR(VLOOKUP($A2,Products,3,FALSE)*$B2,0) looks up a price by product code, multiplies by quantity, and returns 0 when the code is missing.