MS Excel
🔒 Log in to trackFunctions you must know
🔒 Log in to track| Category | Functions |
|---|---|
| Maths | SUM, SUMIF, PRODUCT, ROUND, INT, MOD, ABS, POWER, SQRT |
| Statistics | AVERAGE, COUNT, COUNTA, COUNTBLANK, COUNTIF, MAX, MIN, MEDIAN, MODE, RANK, LARGE, SMALL, STDEV |
| Logical | IF, AND, OR, NOT, IFS, IFERROR |
| Text | LEN, LEFT, RIGHT, MID, CONCATENATE/CONCAT, TRIM, UPPER, LOWER, PROPER |
| Date-time | TODAY, NOW, DAY, MONTH, YEAR, DATEDIF |
| Lookup | VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP (2021+) |
| Financial | PMT (loan instalment), FV, PV, NPV |
Count-family (classic trap): COUNT counts numeric cells only; COUNTA counts all non-empty cells; COUNTBLANK counts empty cells; COUNTIF counts cells meeting a condition (e.g. COUNTIF(A1:A10,">80")).
Lookup: VLOOKUP(value, table, col_index, FALSE) searches the first column of the table vertically and returns the matching row's col_index-th column; FALSE = exact match. HLOOKUP works horizontally on the first row.
Text/date: LEN counts characters (LEN("SSCCGL") = 6); TRIM strips extra spaces; PROPER capitalises Each Word; UPPER/LOWER change case. TODAY() gives today's date (updates); NOW() gives date and time.
Detailed notes
Formulas start with =
Every Excel calculation begins with the = sign: =A1+B1. Without it, Excel treats "A1+B1" as text. Calculations follow BODMAS order; functions are written as =NAME(arguments) with commas between arguments.
The counting family — the classic exam trap
Range for all examples below: A1=50, A2=70, A3="exam", A4 empty.
| Function | Job | Result here |
|---|---|---|
| =SUM(A1:A4) | adds numbers | 120 |
| =AVERAGE(A1:A4) | mean of the numbers | 60 |
| =MAX / =MIN | largest / smallest number | 70 / 50 |
| =COUNT | counts cells holding numbers | 2 |
| =COUNTA | counts all non-empty cells | 3 |
| =COUNTBLANK | counts empty cells | 1 |
COUNT counts Numbers; COUNTA counts Anything. Text and blank cells are silently skipped by SUM, AVERAGE and COUNT — that is why COUNTA exists.
Maths and logic functions
- =ROUND(1234.567, 2) gives 1234.57 (round to 2 decimals); =INT(8.9) gives 8 (chops the fraction); =MOD(10, 3) gives the remainder 1; =ABS(-9) gives 9; =SQRT(144) gives 12; =POWER(2, 10) gives 1024.
- =IF(test, yes-value, no-value): =IF(A1>=40, "Pass", "Fail") prints Pass when A1 holds 50.
- =COUNTIF(range, criteria): =COUNTIF(A1:A10, ">50") counts how many cells exceed 50.
Text functions
=LEFT("EXAM", 2) gives EX; =RIGHT("EXAM", 3) gives XAM; =MID("computer", 4, 3) gives put (start at 4th letter, take 3); =LEN("computer") gives 8; =UPPER/=LOWER/=PROPER change case (PROPER("india gate") gives India Gate); =CONCATENATE("SS","C") or "SS"&"C" joins text.
Lookup and date
- =VLOOKUP(value, table, column-number, FALSE) searches for the value in the first (leftmost) column of the table and returns the matching entry from the chosen column. The FALSE asks for an exact match. The V means the search runs vertically down the first column.
- =TODAY() shows today's date, =NOW() shows date and time — both recalculate every time the sheet opens. For a fixed stamp, press Ctrl+; (date) or Ctrl+Shift+; (time).
Reading a function question in the exam
- Write down the cell values from the stem in a small table.
- Apply the function's rule, remembering what it IGNORES (text, blanks) or where it LOOKS (first column for VLOOKUP).
- Compute with the ignored cells removed — then match. Distractors are always built from the mistakes: dividing by the full range length, counting text in COUNT, or taking MID from position 0.
Quick revision
- Every formula starts with =; arguments separated by commas.
- COUNT = numbers only, COUNTA = anything non-empty, COUNTBLANK = empties.
- ROUND rounds, INT chops, MOD gives remainder, SQRT, POWER.
- IF(test, yes, no); COUNTIF counts cells meeting a condition.
- LEFT/RIGHT/MID/LEN/PROPER for text; & joins text.
- VLOOKUP searches the first column vertically; TODAY/NOW recalculate.
Types of questions asked
Every way this subtopic shows up in exams — how to recognise it, the formula or logic to use, and a solved example.
Type 1: SUM / AVERAGE output computationvery common4 practice Q
A small cell list is given and the output of =SUM or =AVERAGE is asked; options include realistic wrong totals.
- Add the numeric cells for SUM; divide the total by the count of NUMERIC cells for AVERAGE.
- Text and blank cells are skipped by both — AVERAGE never counts them in the denominator.
- Verify by hand once, then match the option.
Example: A1=10, A2=20, A3=30. What does =AVERAGE(A1:A3) return?
(10+20+30)/3 = 60.
Type 2: COUNT family trapvery common4 practice Q
A range mixing numbers, text and blanks is described and COUNT / COUNTA / COUNTBLANK outputs are asked — the options differ by exactly the text or blank cells.
- COUNT counts only numeric cells; COUNTA counts every non-empty cell; COUNTBLANK counts empties.
- Count the mixture carefully: numbers first, then everything filled, then the gaps.
- COUNTA + COUNTBLANK = total cells in the range.
Example: A1=5, A2='word', A3 blank, A4=9. =COUNT(A1:A4) and =COUNTA(A1:A4) give —
COUNT = 2 (only 5 and 9 are numbers); COUNTA = 3 (all filled cells, text included).
Type 3: Maths function outputcommon5 practice Q
Outputs of ROUND, INT, MOD, ABS, SQRT, POWER on given numbers, or the function that returns a remainder / square root / rounded value.
- ROUND(number, digits) rounds to that many decimals; INT chops the fraction for positives.
- MOD(a, b) = remainder of a divided by b; ABS removes the minus; SQRT is the square root; POWER(a, b) raises a to the power b.
- Beware INT of a negative number rounds DOWN (towards minus infinity).
Example: What does =MOD(23, 5) return?
3 — 23 divided by 5 is 4 with remainder 3; MOD returns the remainder.
Type 4: Text and date functionscommon5 practice Q
Outputs of LEFT, RIGHT, MID, LEN, PROPER, CONCATENATE, or the function that stamps today's date / joins text.
- LEFT takes letters from the start, RIGHT from the end, MID(text, start, how-many) from the middle; LEN counts characters (spaces included).
- UPPER/LOWER/PROPER set the case; CONCATENATE or the & sign joins text.
- TODAY() and NOW() recalculate on every open; Ctrl+; and Ctrl+Shift+; stamp fixed values.
Example: What does =LEN('keyboard') return?
8 — LEN counts the characters of the text; keyboard has 8 letters.
Type 5: IF and VLOOKUP logiccommon4 practice Q
The output of =IF(condition, x, y) for a given cell value, or which column VLOOKUP searches / what the FALSE argument means.
- IF tests the condition and returns the second argument when true, the third when false.
- VLOOKUP searches the FIRST (leftmost) column of the table vertically and returns from the chosen column number; FALSE asks for an exact match.
- Write the condition with the given value substituted, then pick the branch.
Example: =IF(B2>=40, 'Pass', 'Fail') with B2 = 38 returns —
Fail — 38 is not greater than or equal to 40, so the third argument is chosen.
Shortcut tricks
⚡ COUNT counts Numbers, COUNTA counts Anything
COUNT = digits only; COUNTA = everything non-empty (text too); COUNTBLANK = the gaps. Test any MCQ by tagging each cell in the range N (number), T (text), E (empty).
Example: Range has 3 numbers, 1 text cell, 1 blank. COUNT? COUNTA?
COUNT = 3; COUNTA = 4.
⚡ V of VLOOKUP = Vertical
VLOOKUP hunts down the first column; HLOOKUP along the first row. The 4th argument FALSE = exact match ('F for Full match').
Example: VLOOKUP searches the table for the lookup value in which direction?
Vertically, in the first column.
⚡ TODAY vs NOW
TODAY() = date only; NOW() = date + time. Both refresh on recalculation (unlike Ctrl+; which stamps a fixed date).
Example: Function returning current date and time together?
NOW().
Where students lose marks
Letting COUNT count text cells - it skips them silently.
Looking up VLOOKUP values in any column - the lookup value must be in the table's first column.
Believing TODAY() freezes the date - it recalculates; Ctrl+; stamps a static date.
Practice sets — 27 questions
Sets of 10, mixed across the question types above. Each answer comes with a step-by-step explanation.
Topic test · 10 questions
Suggested time 6 min · wrong answers go to your mistake notebook automatically.