References, formulas, and reusable calculations

Overview

The owner of Juniper Print wants to compare three months of planned notebook sales. She needs formulas that follow each month's volume while continuing to use the same price and cost assumptions.

Both businesses and all figures in this lesson are fictional teaching assumptions.

Recall Lesson 029: Why should you enter a price in an input cell rather than bury it inside every revenue formula?

References that move or stay fixed

A cell reference identifies a spreadsheet box by column and row. B8 means column B, row 8. A formula starts with =; * multiplies and - subtracts.

A relative reference changes when you copy a formula to another location. Copying =B8*B2 one column right changes it to =C8*C2. Both references move. That is useful for monthly quantities, but wrong if the common price remains in B2.

An absolute reference keeps the named column and row fixed when copied. Write $B$2 to hold both. Thus =B8*$B$2, copied one column right, becomes =C8*$B$2. The quantity moves to the next month; the price stays linked to B2. Microsoft documents this distinction. Reference guide.

The dollar signs control copying; they do not turn the value into currency or prevent someone editing B2. A mixed reference fixes only one part, such as $B2 for column B or B$2 for row 2. Today, relative and fully absolute references are enough.

Set up the inputs

Use the following cell addresses on one sheet. Enter amounts as numbers, with labels beside them. These assumptions apply to each of three equal-length planning months.

Put Month A, Month B, and Month C in B7, C7, and D7. Enter planned units of 60, 80, and 100 in B8, C8, and D8. Column A holds row labels.

Assume one notebook type, all units sold in the month produced, enough production capacity and funding, and no other expenses or income taxes. Fixed costs stay at $600 each month. Sales quantities are estimates; prices and costs are planning assumptions. This model measures monthly profit, not cash balances.

Worked example

Enter these formulas in column B. Copy B9:B12 and paste into C9:C12, then D9:D12. Copy the cells containing formulas, rather than retyping their displayed results.

For Month A, revenue is 60 × $20 = $1,200. Variable costs are 60 × $8 = $480. Profit is $1,200 − $480 − $600 = $120.

In Month B, the revenue formula becomes =C8*$B$2. Profit becomes =C9-C10-C11. Those relative references now use Month B's calculated results.

Check one copied formula before trusting the rest. A formula pointing at an empty price cell might return zero without an obvious error message.

Sum a range

A function is a named, built-in calculation. SUM adds values. A range is a group of cells; B12:D12 includes B12, C12, and D12. The colon means “through.” Microsoft's SUM documentation explains adding ranges this way. SUM guide.

In E7, label the column “Three-month total.” Enter =SUM(B9:D9) in E9 and copy it down through E12. Total profit is $120 + $360 + $600 = $1,080. Total revenue of $4,800 minus variable costs of $1,920 and fixed costs of $1,800 also equals $1,080.

Sum comparable amounts across matching periods. Do not include E12 inside its own sum: that would make the answer depend on itself, creating a circular reference.

Flag a condition

The IF function tests a condition and returns one result when it is true and another when false. Its structure is =IF(test,result_if_true,result_if_false). Text results go inside quotation marks. Microsoft's IF guide explains this structure.

Label A13 “Monthly target status.” In B13 enter =IF(B12>=$B$5,"Meets target","Below target") and copy across through D13. The symbol >= means greater than or equal to, so exactly $300 qualifies.

Month A displays “Below target”; Months B and C display “Meets target.” The formula tests only profit against the target. It does not verify demand, capacity, or funding. These examples use English function names and commas; some regional settings use semicolons between arguments.