Perform operations by using formulas and functions
The Perform operations by using formulas and functions domain covers references and the function set the exam expects: logical, lookup, conditional aggregation, text, date, rounding, and error handling. You need to know argument order and what each function counts or returns.
References and function rules
- Relative references (
A1) change when copied; absolute references ($A$1) do not; mixed references lock only the row or column. - 3-D references span sheets:
=SUM(Jan:Mar!B2). Named ranges and structured references can replace A1 references. - Logical functions include IF, AND, OR, and NOT. Error handling uses IFERROR and ISERROR.
- SUMIFS, COUNTIFS, and AVERAGEIFS require equal-sized criteria ranges; text criteria go in quotes.
VLOOKUP(lookup_value, table_array, col_index_num, FALSE)performs an exact match. INDEX/MATCH avoids VLOOKUP's left-column limitation.- Text functions include LEFT, RIGHT, MID, LEN, TRIM, CONCAT, TEXTJOIN, UPPER, LOWER, and PROPER. Date functions include TODAY, NOW, DATE, YEAR, MONTH, DAY, and EOMONTH. Rounding functions include ROUND, ROUNDUP, ROUNDDOWN, and INT.
Worked example
=IFERROR(VLOOKUP($A2,Products!$A$2:$D$50,4,FALSE),"Not found"). The $A2 locks the lookup column so the formula can copy across and down. The table range is absolute. The 4 returns the fourth column, and FALSE forces an exact match. Without IFERROR, a missing product returns #N/A. If the fourth argument were TRUE or omitted, VLOOKUP could return an approximate match.
Traps
VLOOKUP defaults to approximate matching. COUNT counts numbers, COUNTA counts nonblank cells, and COUNTBLANK counts blanks. SUMIFS and COUNTIFS ranges must match in size. Dates stored as text do not calculate correctly. Missing absolute references cause wrong results when copied. A circular reference prevents normal calculation.