Intermediate level · Module 2: Excel for Everyday Office Data · Lesson 1 of 4
References and Conditional Functions
Write formulas that copy correctly and calculate based on conditions.
In this lesson you will
- Use absolute references with $
- Apply IF, COUNTIF and SUMIF to real records
Key concepts
absolute referenceIFCOUNTIFSUMIF
The lesson
When you copy a formula, relative references (B2) change; absolute references ($B$2) stay fixed. Put values such as an exchange rate in one cell and refer to it absolutely, so updating the rate updates every calculation.
IF makes a decision: =IF(C2>500,"Above limit","OK"). COUNTIF and SUMIF count or add only the rows that meet a condition, such as all allowances for the Finance department.
Check conditional formulas with values just below, at and above the limit, as you would test any decision.
Step by step
- Put the exchange rate in a labelled cell.
- Calculate USD amounts with an absolute reference to that cell.
- Flag amounts above the limit with IF.
- Total each department with SUMIF.
Field allowances in SSP and USD with one exchange-rate cell
How an absolute reference keeps every conversion correct when the rate changes.
| Item | Working | Amount |
|---|---|---|
| Exchange rate (B1) | typed once, labelled | 1 USD = 4,500 SSP |
| Akol — 6 days × 45,000 SSP | =C4*D4 | 270,000 SSP |
| Akol in USD | =E4/$B$1 | 60.00 USD |
| Mary — 12 days × 45,000 SSP | =C5*D5 | 540,000 SSP |
| Mary in USD | =E5/$B$1 | 120.00 USD |
| Limit check (above 100 USD?) | =IF(F5>100,"Above limit","OK") | Above limit |
| Total in USD | =SUM(F4:F5) | 180.00 USD |
Explanation
The rate lives in one labelled cell. $B$1 is an absolute reference, so when the formula is copied down, every row still divides by B1. If the rate changes, update B1 and every USD figure updates.
The IF formula flags Mary's allowance for approval because it exceeds the 100 USD limit — a rule that would be easy to miss by eye in a long list.
Check your understanding
If the rate in B1 changes to 5,000 SSP, what is Mary's allowance in USD? (540,000 ÷ 5,000 = 108.00 USD — still above the limit.)
Practice activity
In a staff allowance sheet, calculate allowances with a fixed exchange-rate cell, flag 'Above limit' with IF, and total by department with SUMIF.
Expected result: Correct allowances, flags and department totals.
Check your understanding
-
When do you need an absolute reference?
Show answer
When every copied formula must refer to the same fixed cell, such as a rate or target.
Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.