AccountsISC Class 12 · Accounting Application of Electronic Spread Sheet

Spreadsheet Formulas, Functions and Accounting Applications: ISC Class 12

Updated 17 October 2026 · Draft, not yet fact-checked

A spreadsheet formula starts with = and uses cell references and operators (+, −, *, /, %), for example =SUM(B2:F2). Functions such as SUM, AVERAGE, IF, TODAY and DATE do ready-made calculations. The ISC syllabus applies these to payroll (Basic, HRA, DA, TA, CCA, CTC, PF, income tax), profit and commission on sales, total and average marks, and journal entries.

Notice that each result cell has a formula starting with =: SUM adds the allowances to Gross, and subtracting PF gives Net Salary.

Illustration drawn for QuestionBuddy.

Understand Spreadsheet Formulas, Functions and Accounting Applications

Formulas. Every formula begins with an equals sign. Operators: + (summation), − (subtraction), * (multiplication), / (division), and % (percentage). Examples: =B2+C2; =B2-C2; =B2*C2; =B2/C2; =40%*B2 or =B2*40/100. Brackets control the order: multiplication and division are done before addition and subtraction.

Functions.

=SUM(B2:F2) adds a range; =AVERAGE(B2:B6) gives the average.
If-then-else: =IF(condition, value if true, value if false). Example: =IF(G2>50000,10%*G2,5%*G2). IF can be nested: =IF(F2>=75,"A",IF(F2>=60,"B",IF(F2>=40,"C","D"))). Test the highest threshold first, or the first true test will catch everything.
Date functions: =TODAY() gives the current date; =DATE(2026,4,1) builds a date; =YEAR(B2), =MONTH(B2), =DAY(B2) extract parts. A date is stored as a number, so =B2+30 gives the date 30 days later.
Freeze Panes is the screen feature covered on the previous topic page.

1. Payroll. Components: Basic; HRA (house rent allowance); DA (dearness allowance); TA (transport allowance); CCA (city compensatory allowance); Gross = sum of these; deductions: PF (provident fund) and income tax; Net salary = Gross − deductions; CTC (cost to company) as the question defines it, usually Gross plus the employer's PF contribution. The percentages and slabs are always given in the question; the rates in this page are assumptions for the exercises.

2. Sale of goods. Profit on sales = Sales − Cost of goods; profit percentage = Profit ÷ Sales × 100. Commission or cost of salesmen is a percentage of sales, often by an IF rule (for example 2% up to ₹1,00,000 of sales and 3% above).

3. Marks obtained by students. Total = SUM of subject marks; Average = Total ÷ number of subjects, or AVERAGE(); result or grade by IF.

4. Business transactions: Journal. A journal sheet has columns for Date, Particulars, L.F., Debit amount and Credit amount. A check formula such as =IF(SUM(D2:D5)=SUM(E2:E5),"Balanced","Check") confirms that total debits equal total credits.

Copying formulas. Write the formula once, then fill it down. Relative references adjust row by row; lock a constant such as a tax rate by making its reference absolute.

Spreadsheet Formulas, Functions and Accounting Applications: key facts

Gross salary: Basic + HRA + DA + TA + CCA | =SUM(B2:F2); Components as defined in the question.
Net salary: Gross salary − PF − Income tax | =G2-H2-I2; PF and tax are deductions.
CTC: Gross salary + Employer's PF contribution | as defined in the question; Check the definition given.
IF function: =IF(condition, value if true, value if false) | nest IF for more outcomes; Nest IF for more than two outcomes.
Marks sheet: Total =SUM(B2:F2)
Average =AVERAGE(B2:F2)
Percentage = Total ÷ Maximum × 100; Per student.
Profit on sales: Sales − Cost | Profit % = Profit ÷ Sales × 100; Commission by IF rule.

How to answer Spreadsheet Formulas, Functions and Accounting Applications questions

Treat each question as: columns, formula for the first row, then fill down.

1Identify each column and its heading from the question.
2Write the formula for the first data row using cell references, not numbers.
3Use IF where a rule depends on a condition, testing the correct boundary (> or >=).
4Fill down, then verify one row by hand.
5State the result values if the data are given.

Spreadsheet Formulas, Functions and Accounting Applications 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.

A rule in a cell

Suppose a shop pays its sales staff 2% commission on sales up to ₹1,00,000 and 3% on total sales if above ₹1,00,000. Cell B2 holds the monthly sales.

Write the formula for commission and find the commission on sales of ₹1,20,000 and on ₹1,00,000.

Try it first, then show the solutionSolution

Formula: =IF(B2>100000,3%*B2,2%*B2).
For 1,20,000: the test is true, so 3% × 1,20,000 = ₹3,600.
For 1,00,000: the test is false (not greater), so 2% × 1,00,000 = ₹2,000.

Answer: =IF(B2>100000,3%*B2,2%*B2); commission ₹3,600 and ₹2,000.

Memory hook: The comparison sign decides who gets the higher rate.

Tricks to remember Spreadsheet Formulas, Functions and Accounting Applications

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

Equals first
No = sign, no formula: the cell shows plain text.
Use it when: Entering formulas.
Test high to low
In a nested IF, test the highest limit first.
Use it when: Grades and slabs.
Dr = Cr check
Journal sheet check: total debit equals total credit.
Use it when: Journal application.

Spreadsheet Formulas, Functions and Accounting Applications: solved examples

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

Example 1: AVERAGE function · 1 mark · MCQBoard level

Cells B2:B5 contain 40, 60, 50 and 70. The value shown by =AVERAGE(B2:B5) is:
(a) 220
(b) 55
(c) 50
(d) 60

Try it first, then show the solutionSolution

Sum = 40 + 60 + 50 + 70 = 220.
Average = 220 ÷ 4 = 55.

Answer: (b) 55

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

In a payroll sheet, B2 holds Basic, C2 HRA, D2 DA, E2 TA, F2 CCA, G2 Gross, H2 PF and I2 Income tax. Write formulas for: HRA at 40% of Basic; Gross salary; Net salary in J2. (3 marks)

Try it first, then show the solutionSolution

HRA in C2: =40%*B2.
Gross in G2: =SUM(B2:F2).
Net in J2: =G2-H2-I2.

Answer: C2: =40%*B2; G2: =SUM(B2:F2); J2: =G2-H2-I2 (equivalent forms accepted).

Example 3: Payroll for three employees · 6 marks · Long answerHarder board level

Prepare the payroll for Ravi (Basic ₹30,000), Sita (₹24,000) and Tara (₹45,000). HRA is 40% and DA 20% of Basic; TA is ₹2,000 and CCA ₹1,500 for each. PF is 12% of Basic. Income tax is 10% of Gross if Gross exceeds ₹50,000, otherwise 5% of Gross. CTC is Gross plus an employer PF equal to the PF above. Show the sheet with the IF formula for tax, and values. (6 marks)

Try it first, then show the solutionSolution

Tax formula in I2: =IF(G2>50000,10%*G2,5%*G2).
Ravi: HRA 12,000, DA 6,000; Gross = 30,000 + 12,000 + 6,000 + 2,000 + 1,500 = 51,500; PF 3,600; tax 10% = 5,150; Net = 51,500 − 3,600 − 5,150 = 42,750; CTC = 55,100.
Sita: Gross = 24,000 + 9,600 + 4,800 + 2,000 + 1,500 = 41,900; PF 2,880; tax 5% = 2,095; Net = 36,925; CTC = 44,780.
Tara: Gross = 45,000 + 18,000 + 9,000 + 2,000 + 1,500 = 75,500; PF 5,400; tax 10% = 7,550; Net = 62,550; CTC = 80,900.

Answer:

Employee	Basic	HRA	DA	TA	CCA	Gross	PF	Income tax	Net salary	CTC
Ravi	30,000	12,000	6,000	2,000	1,500	51,500	3,600	5,150	42,750	55,100
Sita	24,000	9,600	4,800	2,000	1,500	41,900	2,880	2,095	36,925	44,780
Tara	45,000	18,000	9,000	2,000	1,500	75,500	5,400	7,550	62,550	80,900

Example 4: Commission on sales · 3 marks · Short answerHarder board level

Salesmen Rahul, Meera and Imran had sales of ₹80,000, ₹1,50,000 and ₹1,00,000 at cost of ₹62,000, ₹1,10,000 and ₹78,000. Commission is 3% of sales if sales exceed ₹1,00,000, otherwise 2%. Write the formulas for profit and commission and give the values. (3 marks)

Try it first, then show the solutionSolution

Profit in D2: =B2-C2. Commission in E2: =IF(B2>100000,3%*B2,2%*B2).
Rahul: profit 18,000; commission 2% = 1,600.
Meera: profit 40,000; commission 3% = 4,500. Imran: profit 22,000; commission 2% = 2,000 as sales are not above 1,00,000.

Answer:

Salesman	Sales	Cost	Profit	Commission
Rahul	80,000	62,000	18,000	1,600
Meera	1,50,000	1,10,000	40,000	4,500
Imran	1,00,000	78,000	22,000	2,000

Example 5: Grade formula with the wrong order · 3 marks · Short answerHOTS / competency

Grades are: average 75 or more is A; 60 or more is B; 40 or more is C; below 40 is D. A teacher uses =IF(F2>=40,"C",IF(F2>=60,"B",IF(F2>=75,"A","D"))) and every passing student gets C. Explain the error, write the correct formula, and give the grades of Ana (marks 78, 64, 82, 55, 71), Bo (35, 42, 28, 50, 45) and Cy (90, 88, 92, 95, 85). (3 marks)

Try it first, then show the solutionSolution

The first test (>=40) is true for every passing student, so the later tests are never reached.
Correct: =IF(F2>=75,"A",IF(F2>=60,"B",IF(F2>=40,"C","D"))).
Averages: Ana 350 ÷ 5 = 70, grade B; Bo 200 ÷ 5 = 40, grade C; Cy 450 ÷ 5 = 90, grade A.

Answer: The tests are in the wrong order; test 75 first. Correct formula: =IF(F2>=75,"A",IF(F2>=60,"B",IF(F2>=40,"C","D"))).

Student	Total	Average	Grade
Ana	350	70	B
Bo	200	40	C
Cy	450	90	A

Where students lose marks in Spreadsheet Formulas, Functions and Accounting Applications

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

Mistake: Typing values instead of references
Calculating in the head and typing the result.
Wrong working
=42,750
Right working
=G2-H2-I2
Do this: Always use cell references.
Usually costs: 1-2 marks.
Mistake: Using >= instead of > in the commission rule
Not reading "exceeds" carefully.
Wrong working
=IF(B4>=100000,3%*B4,2%*B4) gives 3,000 for Imran
Right working
=IF(B4>100000,3%*B4,2%*B4) gives 2,000
Do this: Exceeds means >; at least means >=.
Usually costs: 1-2 marks.
Mistake: Writing the nested IF in the wrong order
Starting with the lowest limit.
Wrong working
=IF(F2>=40,"C",IF(F2>=60,"B",...))
Right working
=IF(F2>=75,"A",IF(F2>=60,"B",IF(F2>=40,"C","D")))
Do this: Test the highest threshold first.
Usually costs: 2 marks.
Mistake: Missing the = sign
Treating a formula as text.
Wrong working
SUM(B2:F2)
Right working
=SUM(B2:F2)
Do this: Every formula starts with =.
Usually costs: 1 mark.

Exam tips

Write the formula for the first row only, plus the fill-down.
Use the cell addresses given in the question; if none are given, define your own columns.
Check the boundary in every IF.
Use TODAY() for the current date and DATE(y,m,d) for a fixed date.

Practice questions on Spreadsheet Formulas, Functions and Accounting Applications

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
B2 contains 35. The value of =IF(B2>=40,"Pass","Fail") is:
(a) Pass
(b) Fail
(c) 35
(d) an error
Hint
35 is below 40.
Answer
(b) Fail
Q2 · 3 marks · Short answerBoard level
B2 contains the date 15-Mar-2026 and the credit period is 45 days. (i) Write the formula for the due date. (ii) What date does it show? (iii) A student scored a total of 350 out of 500; write the formula for percentage if the total is in B3. (3 marks)
Hint
A date plus a number of days.
Answer
(i) =B2+45. (ii) 29-Apr-2026 (16 days to the end of March, then 29 more). (iii) =B3/500*100, which gives 70.
Q3 · 3 marks · Short answerHarder board level
In a journal sheet, D2:D5 (Debit) hold 25,000; 0; 60,000; 0 and E2:E5 (Credit) hold 0; 25,000; 0; 60,000. Write a formula in D6 that checks the journal and state what it displays. (3 marks)
Hint
Compare two sums.
Answer
=IF(SUM(D2:D5)=SUM(E2:E5),"Balanced","Check"). Both totals are 85,000, so it displays Balanced.
Q4 · 6 marks · Long answerHarder board level
Prepare the payroll of Kiran (Basic ₹20,000) and Lata (₹35,000). HRA is 30% and DA 10% of Basic; TA is ₹1,800 and there is no CCA. PF is 12% of Basic. Income tax is nil if Gross is ₹30,000 or less, otherwise 10% of the Gross above ₹30,000. CTC is Gross plus an employer PF equal to the PF. Write the tax formula and give Gross, PF, tax, Net salary and CTC. (6 marks)
Hint
The tax is on the excess over 30,000.
Answer
Tax: =IF(G2>30000,10%*(G2-30000),0). Kiran: Gross 29,800; PF 2,400; tax 0; Net 27,400; CTC 32,200. Lata: Gross 50,800; PF 4,200; tax 2,080; Net 44,520; CTC 55,000.
Q5 · 3 marks · Short answerHOTS / competency
The manager says Imran, whose sales were exactly ₹1,00,000, should get the higher 3% commission and asks the clerk to change the formula to >=. By how much does Imran's commission change, and what other effect could this change have on a salesman at exactly ₹1,00,000 in future? (3 marks)
Hint
3% versus 2% of 1,00,000.
Answer
Commission rises from 2% × 1,00,000 = ₹2,000 to 3% × 1,00,000 = ₹3,000, a rise of ₹1,000. Any salesman with sales of exactly ₹1,00,000 would also get 3%, so the rule must match the policy given.

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.How does every formula start?Show answer
With the = sign.
2.IF function syntax?Show answer
=IF(condition, value if true, value if false).
3.Net salary formula?Show answer
Gross − PF − income tax.
4.Which function gives today's date?Show answer
=TODAY().
5.Which journal check is used?Show answer
Total debit equals total credit.

Spreadsheet Formulas, Functions and Accounting Applications: frequently asked questions

Are the PF and tax rates in these examples official?

No. They are the assumed rates of the exercise; in the exam, use the rates given in the question.

Is CTC always gross plus employer PF?

No; use the definition given in the question.

Which software should I write formulas for?

Use the standard spreadsheet notation (=, SUM, IF); the ISC marking scheme accepts equivalent forms.

How do I show a percentage in a formula?

Either =40%*B2 or =B2*40/100.