AccountancyCBSE Class 12 · Spreadsheet

Spreadsheet Formulas, Functions and Cell References: Class 12 Rules and Solved Questions

Updated 15 October 2026 · Fact-checked

A spreadsheet formula begins with = and combines cell references, operators and functions such as SUM, AVERAGE, IF and SUMIF. Excel calculates brackets first, then exponents, then multiplication and division, then addition and subtraction. Cell references are relative (change when copied), absolute (fixed with dollar signs) or mixed (only the row or the column fixed).

When the formula is copied down, the relative reference B2 moves to B3 and B4, but the absolute reference $C$1 stays fixed.

Illustration drawn for QuestionBuddy.

Understand Formulas, Functions and Cell References

Formulas. A formula always starts with =. It can contain numbers, cell references (B2), ranges (B2:B10), operators (+, −, *, /, ^) and functions. Order of evaluation: brackets → exponent (^) → multiplication and division → addition and subtraction (left to right within a level). So =10+20*2^2 gives 90, while =(10+20)*2 gives 60.

Common functions.

SUM(range), AVERAGE(range) (ignores blank cells), COUNT(range) (counts numbers), MAX, MIN, ROUND(number, digits).
SUMIF(range, criteria, [sum_range]) adds values meeting a condition; COUNTIF(range, criteria) counts them.
IF(condition, value_if_true, value_if_false): tests a condition that must give TRUE or FALSE.
AND(c1, c2, ...) is TRUE only if all conditions are true; OR(c1, c2, ...) is TRUE if any one is true. They are usually placed inside IF.
Date functions: TODAY(), NOW(), DATE(y, m, d), YEAR(), MONTH(), DAY().

Cell references (what happens when a formula is copied).

Type	Written as	On copying
Relative	C4	both column and row change
Absolute	＄C＄4	nothing changes
Mixed (column fixed)	＄C4	only the row changes
Mixed (row fixed)	C＄4	only the column changes

Pressing F4 while editing cycles through these forms. Use an absolute reference for a single rate or date that every row needs, for example =B2*＄C＄1 for commission at the rate in C1.

Auto-calculate and status bar. Selecting a range shows its sum, average and count on the status bar without a formula.

Errors. A wrong function name gives #NAME?, division by zero #DIV/0!, wrong data type #VALUE!, and a deleted reference #REF!.

Formulas, Functions and Cell References: key facts

Order of operations: 1.( )
2.^
3.* and /
4.+ and −
IF: =IF(condition, value_if_true, value_if_false)
IF with AND: =IF(AND(cond1, cond2), x, y); OR works the same way.
SUMIF and COUNTIF: =SUMIF(range, criteria, sum_range); =COUNTIF(range, criteria)
References: C4 relative; ＄C＄4 absolute; ＄C4 and C＄4 mixed; F4 toggles.

How to answer Formulas, Functions and Cell References questions

Spreadsheet questions test three skills: evaluating a formula, writing one, and predicting it after copying.

1Evaluating: apply the order of operations step by step.
2Writing: start with =, use cell references (not typed numbers) so results update.
3Conditions: decide the test, the value if true and the value if false; combine tests with AND or OR.
4Copying: shift relative parts by the number of rows and columns moved; keep parts with a dollar sign fixed.
5Choose absolute references for rates, dates or totals used in every row.
6Check for error values and their causes.

Formulas, Functions and Cell References in real life

The idea, met in an everyday situation. Answer the question in your own words, then compare with the model answer. The one-line hook is what to remember.

Absolute reference for a commission rate

Suppose a sales manager lists each salesperson's sales in B2:B20 and types the commission rate, 5%, once in cell C1. She writes =B2*C1 in D2 and copies it down, but every row below D2 shows 0.

Why did this happen, and what formula should she use?

Try it first, then show the solutionSolution

Copying =B2*C1 down one row gives =B3*C2; C2 is empty, so the result is 0.
The rate cell must not move when the formula is copied.
Use =B2*＄C＄1 in D2 and copy it down.

Answer: The relative reference C1 shifted to C2, C3 and so on, which are blank. Writing =B2*＄C＄1 fixes the rate cell while B2 changes row by row, giving each salesperson's correct commission.

Memory hook: Dollar signs pin a cell in place.

Tricks to remember Formulas, Functions and Cell References

Short memory aids. Say each one aloud once, then use it on a question the same day.

BEMDAS
Brackets, Exponent, Multiply/Divide, Add/Subtract.
Use it when: Evaluating formulas.
Dollar = don't move
A ＄ before the letter fixes the column; before the number fixes the row.
Use it when: Mixed references.
AND all, OR any
AND needs every condition true; OR needs just one.
Use it when: Logical functions.

Formulas, Functions and Cell References: solved examples

Ordered from board level to HOTS. Try each one before you open the solution.

Example 1: Order of operations · 1 mark · MCQBoard level

The value returned by =10+20*2^2 is:
(a) 120
(b) 90
(c) 3600
(d) 60

Try it first, then show the solutionSolution

2^2 = 4; 20*4 = 80; 10 + 80 = 90.

Answer: (b) 90

Example 2: Copying formulas · 3 marks · Short answerBoard level

Cell D2 contains =B2*＄C＄1. What will the formula become when copied to D5? And what does =A＄1 in cell A3 become when copied to C5? (3 marks)

Try it first, then show the solutionSolution

D2 → D5 moves 3 rows down: B2 becomes B5; ＄C＄1 stays: =B5*＄C＄1.
A3 → C5 moves 2 columns right and 2 rows down.
A＄1: column changes A → C, row fixed: =C＄1.

Answer: =B5*＄C＄1 and =C＄1.

Example 3: IF with AND · 3 marks · Short answerHarder board level

Sales are in B2 and region in C2. Write a formula for commission: 5% of sales if sales exceed ₹1,00,000 and the region is North; otherwise 2%. What does it return for sales ₹1,20,000 in North and in South? (3 marks)

Try it first, then show the solutionSolution

=IF(AND(B2>100000, C2="North"), B2*5%, B2*2%).
North: both conditions true → 1,20,000 × 5% = 6,000.
South: second condition false → 1,20,000 × 2% = 2,400.

Answer: Formula as shown; ₹6,000 for North and ₹2,400 for South.

Example 4: SUMIF and COUNTIF · 3 marks · Short answerHarder board level

Sales of four branches are in B2:B5: ₹12,000, ₹8,000, ₹15,000 and ₹5,000. Write formulas to (i) total sales above ₹10,000 and (ii) count branches with sales above ₹10,000, and give the results. (3 marks)

Try it first, then show the solutionSolution

(i) =SUMIF(B2:B5, ">10000") → 12,000 + 15,000 = 27,000.
(ii) =COUNTIF(B2:B5, ">10000") → 2.

Answer: ₹27,000 and 2.

Example 5: Which reference type? · 1 mark · Assertion-reasonHOTS / competency

Assertion (A): In a multiplication table built by copying one formula across rows and columns, mixed references such as ＄A2*B＄1 are needed.
Reason (R): The row numbers must stay in column A and the column headings must stay in row 1 as the formula is copied.
(a) Both A and R are true, and R is the correct explanation of A.
(b) Both A and R are true, but R is not the correct explanation of A.
(c) A is true, but R is false.
(d) A is false, but R is true.

Try it first, then show the solutionSolution

＄A fixes the column of row values; ＄1 fixes the row of column values; R explains A.

Answer: (a) Both A and R are true, and R is the correct explanation of A.

Where students lose marks in Formulas, Functions and Cell References

The slips examiners see most often, and what to write instead.

Mistake: Forgetting the = sign
Typing the expression only.
Wrong working
B2*5%
Right working
=B2*5%
Do this: Every formula starts with =.
Usually costs: 1/2 to 1 mark.
Mistake: Wrong dollar placement
Not knowing which part should stay fixed.
Wrong working
＄C1 for a rate in C1 copied down.
Right working
＄C＄1 (or C＄1) so the row stays fixed.
Do this: Ask which direction you will copy.
Usually costs: 1 mark.
Mistake: Text without quotes in a condition
Forgetting text syntax.
Wrong working
=IF(C2=North, ...)
Right working
=IF(C2="North", ...)
Do this: Text inside formulas goes in double quotes.
Usually costs: 1 mark.
Mistake: Ignoring operator precedence
Reading left to right.
Wrong working
=10+20*2 read as 60.
Right working
= 10 + 40 = 50.
Do this: Multiplication before addition.
Usually costs: 1 mark.

Exam tips

Write formulas exactly, including = and quotes around text.
When asked about copying, state the new formula, not just 'it changes'.
Use cell references instead of numbers so the sheet updates automatically.
Formula and reference questions are the most frequent 1-mark items in the Spreadsheet chapter.

Practice questions on Formulas, Functions and Cell References

Write each answer on paper first. Open the hint only if you are stuck, and the answer only when you have finished.

Q1 · 1 mark · MCQBoard level
A cell reference that does not change when a formula is copied is:
(a) relative
(b) absolute
(c) mixed
(d) circular
Hint
Two dollar signs.
Answer
(b) absolute
Q2 · 1 mark · MCQBoard level
The function that returns TRUE only when all its conditions are true is:
(a) OR
(b) IF
(c) AND
(d) COUNTIF
Hint
All.
Answer
(c) AND
Q3 · 3 marks · Short answerHarder board level
Evaluate: (i) =(10+20)*2; (ii) =ROUND(1234.567, 1); (iii) =AVERAGE(A1:A4) where A1:A4 contain 10, 20, 30 and a blank cell. (3 marks)
Hint
AVERAGE ignores blanks.
Answer
(i) 60; (ii) 1234.6; (iii) 20 (average of the three numbers).
Q4 · 3 marks · Short answerHarder board level
Write a formula in C2 to give a bonus of 10% of salary (B2) if salary is ₹50,000 or more, otherwise 5%. What is the bonus on ₹60,000 and on ₹40,000? (3 marks)
Hint
IF with >=.
Answer
=IF(B2>=50000, B2*10%, B2*5%). ₹6,000 and ₹2,000.
Q5 · 1 mark · Very short answerHarder board level
A formula =＄A1+B＄2 in C3 is copied to E6. What is the new formula? (1 mark)
Hint
2 columns right, 3 rows down.
Answer
=＄A4+D＄2.
Q6 · 1 mark · Assertion-reasonHarder board level
Assertion (A): =OR(B2>=50, C2>=90) returns TRUE when B2 is 45 and C2 is 80.
Reason (R): OR returns TRUE if at least one condition is true.
(a) Both A and R are true, and R is the correct explanation of A.
(b) Both A and R are true, but R is not the correct explanation of A.
(c) A is true, but R is false.
(d) A is false, but R is true.
Hint
Check both conditions.
Answer
(d) A is false, but R is true. Neither 45 ≥ 50 nor 80 ≥ 90 holds, so OR returns FALSE.
Q7 · 3 marks · Short answerHOTS / competency
Suppose you must calculate interest at the rate in cell F1 on deposits listed in B2:B50, and later the rate will change. Explain how you would write and copy the formula so that one change in F1 updates all rows. (3 marks)
Hint
Absolute reference to the input cell.
Answer
Enter the rate once in F1 as an input. In C2 write =B2*＄F＄1 and copy it to C50. B2 changes row by row while ＄F＄1 stays fixed, so changing F1 instantly recalculates every interest amount.
Q8 · 3 marks · Short answerHOTS / competency
A student writes =IF(B2>10000, "High") and sees FALSE for small values. Explain the result and correct the formula to show 'Low' instead. (3 marks)
Hint
Missing value_if_false.
Answer
When value_if_false is omitted, IF returns FALSE if the condition is not met. Correct: =IF(B2>10000, "High", "Low").

Test your recall

Answer in your head or on paper first, then open the card. Pulling an answer out of memory makes it stick far better than re-reading.

1.Which character starts every formula?Show answer
=.
2.Which key toggles reference types?Show answer
F4.
3.What does ＄C4 fix?Show answer
The column C.
4.What does COUNTIF do?Show answer
Counts cells in a range that meet a criterion.
5.What error appears for a misspelt function name?Show answer
#NAME?

Formulas, Functions and Cell References: frequently asked questions

What is the difference between relative and absolute references?

Relative references change when a formula is copied; absolute references (with dollar signs before column and row) stay fixed.

When should I use a mixed reference?

When a formula is copied in two directions and only the row or only the column must stay fixed, as in multiplication tables or rate grids.

What is the difference between SUM and SUMIF?

SUM adds all values in a range; SUMIF adds only those that meet a condition.

Does AVERAGE count blank cells?

No. Blank cells are ignored, though cells containing zero are included.