Excel Formulas, Functions and Cell References
Excel Formulas, Functions and Cell References. Foundational Computer Awareness explanations, examples and explained practice questions for banking and recruitment exam preparation.
By Akhilesh Saini
What you will learn
- How formulas use operators, values and cell references.
- What the common functions SUM, AVERAGE, COUNT, COUNTA, MIN, MAX and IF do.
- How copied formulas behave with relative, absolute and mixed references.
- Simple causes of spreadsheet errors and misleading results.
Why this topic matters
Formula evaluation and cell-reference questions are common computer-aptitude concepts. An exam might give A1=10, A2=20, then ask for =SUM(A1:A2), or show a formula copied from one cell to another and ask how the references change.
Formulas start with an instruction to calculate
In Excel, a typical formula begins with =. A formula can use a constant such as =7+3, a cell reference such as =B2+C2, or a function such as =SUM(B2:B5).
| Symbol | Basic meaning | Example |
|---|---|---|
+ | Add | =A1+B1 |
- | Subtract | =A1-B1 |
* | Multiply | =A1*B1 |
/ | Divide | =A1/B1 |
^ | Power / exponent | =2^3 returns 8 |
() | Group operations | =(2+3)*4 returns 20 |
As in normal arithmetic, precedence matters. For instance, =2+3*4 is 14, whereas =(2+3)*4 is 20. Parentheses are the clearest way to make a calculation's intention explicit.
Built-in functions
A function is a predefined operation that accepts arguments. In the examples below, assume B2:B5 contains the numeric values 10, 20, 30, 40.
| Example formula | Meaning | Result |
|---|---|---|
=SUM(B2:B5) | Add the numbers | 100 |
=AVERAGE(B2:B5) | Arithmetic mean | 25 |
=MIN(B2:B5) | Smallest number | 10 |
=MAX(B2:B5) | Largest number | 40 |
=COUNT(B2:B5) | Count numeric cells | 4 |
=COUNTA(B2:B5) | Count nonempty cells | 4 |
=IF(B2>=20,"Yes","No") | Return one value for true and another for false | No |
COUNT versus COUNTA: COUNT counts cells containing numbers; COUNTA counts cells that are not empty, including text. If A1:A4 contains number 12, text Done, an empty cell and number 8, then COUNT(A1:A4) is 2, while COUNTA(A1:A4) is 3. Some formulas returning an empty string ("") still count as nonempty for COUNTA; a visually blank-looking cell is not always truly empty.
The examples use the familiar comma argument separator. Regional settings and software may use semicolons instead; this does not change the underlying concept.
A complete worked calculation
A branch's fictional stationery expenses are recorded as:
| Cell | Item | Expense (₹) |
|---|---|---|
| B2 | Paper | 120 |
| B3 | Ink | 240 |
| B4 | Files | 140 |
A total cell containing =SUM(B2:B4) produces ₹500 (120 + 240 + 140). A cell with =AVERAGE(B2:B4) produces approximately ₹166.67 when displayed to two decimals; the unrounded arithmetic mean is 500/3. If a displayed result shows two decimals, that does not necessarily mean the underlying calculated value was rounded and replaced.
References: relative, absolute and mixed
A cell reference points to another cell. The dollar sign $ can keep a row or column fixed when a formula is copied.
| Reference | Column behaviour on copy | Row behaviour on copy |
|---|---|---|
B2 | Relative: can shift | Relative: can shift |
$B$2 | Fixed B | Fixed 2 |
$B2 | Fixed B | Relative row |
B$2 | Relative column | Fixed row 2 |
Worked example 1 — relative reference: Cell C2 contains =A2+B2. Copy it one row down to C3: in a normal relative-reference copy, it becomes =A3+B3.
Worked example 2 — absolute reference: Cell C2 contains =B2*$E$1, where E1 holds a fixed rate. Copy down to C3: it becomes =B3*$E$1. The item-row reference changes; the fixed-rate reference does not.
Worked example 3 — mixed reference: Cell C2 contains =$A2*B$1. Copy it one column right and one row down to D3: the result is =$A3*C$1. Column A stays fixed; row 1 stays fixed; the other components shift.
Useful formula warnings
#DIV/0!normally signals a division-by-zero condition.#REF!commonly indicates an invalid cell reference, possibly after referenced cells are deleted.#NAME?can arise from an unrecognised function name or undefined name.#VALUE!often points to an incompatible type of supplied value.######displayed in a cell can mean the column is too narrow to show a numeric/date value; it is not the same as one of the above formula error codes.- A formula can be syntactically valid yet logically wrong. Verify ranges, units, missing cells and whether copied references behave as intended.
Important terms
| Term | Meaning |
|---|---|
| Formula | Expression that calculates a value |
| Function | Named built-in calculation such as SUM |
| Argument | Input supplied to a function |
| Relative reference | Address that normally shifts when copied |
| Absolute reference | Address held fixed with $ markers |
| Mixed reference | Either row or column fixed |
| Range | Group of referenced cells |
| Formula error | Indication of a problem evaluating a formula |
Common confusions and exam traps
- SUM ≠ COUNT: SUM adds numbers; COUNT reports how many numeric entries exist.
- COUNT ≠ COUNTA: text contributes to COUNTA, not normally to COUNT.
B2≠$B$2: the first shifts when copied, the second stays fixed.- Displayed decimal places ≠ stored precision.
=10/0does not yield infinity in an ordinary worksheet calculation; it results in a division-by-zero error.- AVERAGE is not SUM divided by the number of rows in every case: it uses numeric values in its relevant arguments, not necessarily all non-numeric/blank cells.
Objective-exam facts to remember
| Exam asks | Key point |
|---|---|
| Add selected cells | SUM |
| Calculate arithmetic mean | AVERAGE |
| Count numeric entries | COUNT |
| Count nonempty entries | COUNTA |
| Find smallest/largest | MIN / MAX |
| True/false condition | IF |
| Keep both row and column fixed | $A$1 |
| Division by zero | #DIV/0! |
Quick revision
Formulas commonly begin with =. Use functions for repeated calculations, parentheses to make arithmetic clear, and $ when a copied formula must hold a particular row or column fixed. Work a short copy-reference example rather than guessing.
Practice questions — CA-05-03
1. What character commonly starts an Excel formula?
A. #
B. =
C. $
D. @
2. If A1=10 and A2=20, what does =SUM(A1:A2) return?
A. 10
B. 20
C. 30
D. 2
3. What is the result of =2+3*4 with normal spreadsheet precedence?
A. 20
B. 24
C. 9
D. 14
4. What would =AVERAGE(B2:B4) return for B2=10, B3=20 and B4=30?
A. 20
B. 30
C. 60
D. 3
5. A1:A4 contains 12, Done, an empty cell and 8. What is COUNT(A1:A4)?
A. 3
B. 2
C. 4
D. 1
6. For the same four cells, what is COUNTA(A1:A4)?
A. 2
B. 4
C. 3
D. 1
7. C2 contains =A2+B2. Copied one row down to C3, what does the formula become?
A. =A3+B3
B. =A2+B2
C. =$A$2+$B$2
D. =C2+C3
8. Which reference fixes both row and column while copying?
A. B2
B. $B2
C. B$2
D. $B$2
9. C2 contains =B2*$E$1. Copied down to C3, which formula is expected?
A. =B2*$E$2
B. =B3*$E$1
C. =B3E2
D. =C3$E$1
10. C2 contains =$A2B$1. It is copied one column right and one row down to D3. What is the resulting formula?
A. =$B3C$2
B. =$A2B$1
C. =$A3C$1
D. =$B$3*$C$1
11. Which formula error commonly indicates division by zero?
A. #DIV/0!
B. #REF!
C. #NAME?
D. #VALUE!
12. What does =IF(A1>=50,"Pass","Review") return when A1 is 45?
A. 50
B. Pass
C. TRUE
D. Review
Answers and explanations — CA-05-03
1. B — =. The equals sign tells Excel to evaluate the expression as a formula.
2. C — 30. SUM adds the two numeric cells: 10 + 20 = 30.
3. D — 14. Multiplication occurs before addition: 3 × 4 = 12, then 2 + 12 = 14.
4. A — 20. The arithmetic mean is (10+20+30)/3 = 20.
5. B — 2. COUNT counts numeric cells only: 12 and 8.
6. C — 3. COUNTA counts nonempty cells: 12, Done and 8; the genuinely blank cell is excluded.
7. A — =A3+B3. Relative references adjust from row 2 to row 3 when copied down one row.
8. D — $B$2. Dollar signs before the column and row both keep the reference absolute.
9. B — =B3$E$1.* B2 shifts to B3 while absolute $E$1 remains unchanged.
10. C — =$A3*C$1. Column A stays fixed, its row becomes 3; row 1 stays fixed while B shifts to C.
11. A — #DIV/0!. #DIV/0! is Excel's usual division-by-zero error.
12. D — Review. The comparison 45>=50 is false, so IF returns the specified false-result text Review.