Core Formulas
90 मिनट7 पाठ10 प्रश्न
पाठ
1. Formula anatomy + relative/absolute/mixed refs ($) — the magic of F412 min2. SUM, AVERAGE, COUNT vs COUNTA vs COUNTBLANK, MIN, MAX13 min3. IF — the basic building block of conditions13 min4. Nested IF vs IFS — when to switch13 min5. AND, OR, NOT — multiple conditions12 min6. SUMIFS, COUNTIFS, AVERAGEIFS — the most used functions15 min7. IFERROR, IFNA + common error codes (#N/A, #REF!, #VALUE!, #DIV/0!)12 min
अभ्यास फ़ाइल
⬇ डाउनलोड करें m3-formulas-practice.xlsxCore Formulas practice workbook
Use m3-formulas-practice.xlsx throughout the seven lessons. Fill the yellow cells yourself, then compare your work with the Answers sheet. Formulas on Answers are text examples to copy into the indicated sheet; results describe the original inputs.
| Lesson | Sheet | Practice |
|---|---|---|
| 1 | References | Relative totals, fixed GST in F2, and grand totals; change 18% to 12%. |
| 1 | Mixed Refs | Fill B2:M13 with a mixed-reference multiplication formula. |
| 2 | Basic Functions | Calculate seven summaries; compare a real blank, text NA and numbers. |
| 3 | IF Targets | Target status, incentive and a bonus threshold in H1. |
| 4 | Grades | Compare nested IF and IFS at exact grade boundaries; marks are in column A. |
| 5 | Candidates | Selection, shortlist, travel, scholarship and review rules. |
| 6 | Sales | Regional and salesperson totals, product counts and a dynamic product criterion in H1. |
| 6 | Dates | Filter the same sales with real start/end dates in H1/H2, independently of the product criterion. |
| 7 | Errors | Compare raw margins, IF and IFERROR with zero and text inputs; test IFNA separately. |
हिंदी: पीले सेल में फ़ॉर्मूले भरें। कोशिश करने के बाद Answers शीट से फ़ॉर्मूले और नतीजे मिलाएँ। दोनों भाषाओं में शीट के नाम और सेल रेफ़रेंस एक जैसे हैं।
IFS requires a supporting Excel version; use the nested IF alternative when unavailable. Examples use commas between arguments; use semicolons if required by your Excel locale. The workbook contains no macros or external connections. Zero sales, text inputs and the misspelled SUMM examples are intentional error-handling exercises.
मॉड्यूल प्रश्नोत्तरी (10)
1. What does =4+6/2 return?
7
2. Range A1:A5 has 3 numbers, 1 text, 1 blank. What does COUNTA return?
4
3. What does =IF(10>10, "A", "B") return?
B
4. =IF(A1>=50,"C",IF(A1>=75,"B","F")) — what does A1 = 80 return?
C — the order is wrong
5. =AND(TRUE, FALSE)?
FALSE
6. What is the first argument of SUMIFS?
The range to add up
7. What does =IFNA(10/0, "x") return?
#DIV/0! — IFNA only catches #N/A
8. What is locked in $A2?
Column A only
9. Does AVERAGE treat a blank cell as 0?
No, it ignores it
10. 2 conditions in SUMIFS — do they act like OR or AND?
AND