The helper looks right but is text
Keep E numeric. Put TEXT and the final joined label in F only.
Compose a numeric quote and formatted business labels
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Build E2:F7. Keep E numeric: gross = units × price rounded to cents; net = gross × (1 − discount) + signed fee rounded to cents; variance = net − target rounded to cents; discount is the original decimal; validity is DATE(year,month,day); units retain their numeric count. F produces exactly Gross: USD / Net: USD / Variance: USD labels with grouped amounts and two decimals, a + only on positive variance, a one-decimal percentage label, an ISO yyyy-mm-dd validity label, and grouped whole units. Zero money is 0.00 with no sign. Build each helper and label separately; do not replace numeric cells with TEXT results. Inputs: units 0–1,000 whole; price/target 0–1,000,000 whole cents; fee ±1,000,000 whole cents; discount 0–1 in steps of 0.0001; a valid date in 1901–2099. English labels and separators are the declared browser contract; native client locales remain separate.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: F2, E2, E3, E4, E5, E6, E7, F3, F4, F5, F6, F7. Browser calculations may differ from your Excel version.
Numbers for calculation, text for the message
Apply the six numeric helpers in E, then the six labels in F. Each row has a different job, so apply its formula separately. Formatting text does not replace numeric rounding or change the stored helper type.
| Field | Numeric helper | Formatted text |
|---|---|---|
| Gross | E2 — | F2 — |
| Net | E3 — | F3 — |
| Variance | E4 — | F4 — |
| Discount | E5 — | F5 — |
| Validity date | E6 — | F6 — |
| Units | E7 — | F7 — |
Change an actual source value
Apply numeric assumptions: whole units 0–1,000; whole-cent price/target 0–1,000,000; signed fee within ±1,000,000; discount 0–1 in steps of 0.0001; an actual date in 1901–2099. Decimal 0.075 means 7.5%. Text, Booleans, blanks and invalid calendar dates leave the applied source unchanged.
This draft matches the current cell.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Keep a reusable numeric quote beside customer-facing currency, signed variance, discount, date and quantity labels that update with its assumptions.
THE QUICK ANSWER01
TEXT creates a display string from the numeric helper; & joins it to a label. The numeric E column remains available for arithmetic. Apply all six helpers before the six final labels.
E2
=ROUND(B2*B3,2)E3
=ROUND(E2*(1-B4)+B5,2)E4
=ROUND(E3-B6,2)E5
=B4E6
=DATE(B7,B8,B9)E7
=B2F2
="Gross: USD "&TEXT(E2,"#,##0.00;-#,##0.00;0.00")F3
=CONCAT("Net: USD ",TEXT(E3,"#,##0.00;-#,##0.00;0.00"))F4
="Variance: USD "&TEXT(E4,"+#,##0.00;-#,##0.00;0.00")F5
="Discount: "&TEXT(E5,"0.0%")F6
="Valid through: "&TEXT(E6,"yyyy-mm-dd")F7
="Units: "&TEXT(E7,"#,##0")For the example data
E2 2493.75E3 2319.22E4 19.22E5 0.075E6 2026-10-31 00:00E7 125F2 Gross: USD 2,493.75F3 Net: USD 2,319.22F4 Variance: USD +19.22F5 Discount: 7.5%F6 Valid through: 2026-10-31F7 Units: 125
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
Two representations, one source
E holds numbers; F holds text ready for a message. The same report can calculate with E3 while showing Net: USD 2,319.22. Replacing E3 with that label loses its numeric role.
Round business amounts before display
A one-unit order at USD 0.05 with a 50% discount has a numeric net of USD 0.03 after cent rounding. Compute the rounded numeric net first; display formatting must not hide an unrounded helper.
Formatting is an explicit policy
The English report groups thousands, retains two money decimals, displays one percentage decimal and uses an ISO date. This is a teaching output convention, not a promise that every native client locale uses the same separators.
Join alternatives preserve the report
Ampersand, CONCAT and CONCATENATE can combine these scalar text fragments. TEXTJOIN also has delimiter/empty-value behavior; this task does not claim the functions are interchangeable for every array or blank policy.
Read the full step-by-step walkthrough
- Retain numeric helpers
Apply the six E formulas separately. Gross and net are rounded numerically before labels are built; formatting alone does not round the stored numeric business result.
- Declare money formatting
Apply F2 with the ampersand operator. The format uses grouped digits, two decimals and explicit positive/negative/zero sections. USD is a declared label, not an exchange-rate conversion.
- Compare joining methods
F3 uses CONCAT instead of &. For the same gross helper, CONCATENATE("Gross: USD ",TEXT(E2,"#,##0.00;-#,##0.00;0.00")) is another supported construction: replacing only the join method must keep all current and changed-source results.
- Show signed differences
F4 shows a plus on positive variance, a minus on negative variance and unsigned 0.00 at zero. The numeric E4 remains a number regardless of the text sign.
- Format a decimal percentage
E5 retains the original decimal discount. A percent format already multiplies by 100 for display; do not multiply E5 by 100 again.
- Retain dates and whole counts
Build the actual numeric date with DATE in E6 before applying yyyy-mm-dd in F6. F7 formats whole units with grouping. Change a source assumption, inspect all numeric/text pairs, then check the whole report again.
Things to watch
The discount is 100 times too large
0.075 is already 7.5%; use 0.0% without multiplying by 100.
The variance sign or zero is wrong
Positive, negative and zero sections have separate declared results. Do not add a plus unconditionally.
A calendar date silently moves month
The source editor refuses invalid date combinations. DATE normalization is a different operation and is not the business rule here.
About this example
Bounded English formatting over numeric helpers; native locale, recalculation and edit/save certification remain separate.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.