Rounding only the grand total
Half-cent lines expose the difference between line rounding and total rounding.
Accumulate posted charges after rounding each line
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter one formula in G5. Return one numeric total for the eight source records. Include only Status equal to Posted under case-insensitive exact matching; do not trim spaces. For each included record, multiply Units by Unit rate and round that line to two decimals, with ties away from zero, before adding it. Retain negative refunds and zeroes. No posted records returns numeric 0. Do not round a combined raw total instead.
Follow the highlighted cells. Switch to Your turn to edit. Answer cell: G5:G5. Enter one formula in G5; keep the rest of the output area clear. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Use a declared zero initial state, select posted records and add individually rounded signed charges.
THE QUICK ANSWER01
Use a declared zero initial state, select posted records and add individually rounded signed charges.
ƒx
=REDUCE(0,SEQUENCE(8),LAMBDA(total,item,total+IF(INDEX(D2:D9,item)="Posted",ROUND(INDEX(B2:B9,item)*INDEX(C2:C9,item),2),0)))| G |
|---|
| 1.24 |
The remaining reserved output cells are blank. Keep that area clear when trying the formula.
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Rounding order changes the answer
Eight posted lines of 1 × 0.005 each contribute 0.01, giving 0.08. Rounding their combined raw charge gives 0.04 and fails the line-level rule.
Keep the empty selection numeric
The initial state is zero, so an entirely nonposted source produces numeric 0. Negative posted lines reduce the total. Record labels are only for reading; source positions keep the fields aligned.
Edit within the input contract
Units are numeric integers within ±10,000 and rates are numeric from 0 through 1,000 with at most three decimals. Status is one of Posted, Draft or Void, ignoring case and allowing up to three leading or trailing spaces; spaces affect inclusion. Text numbers, blanks and Boolean controls are not numeric inputs. Keep the output area clear. These are fictional teaching prices, not a carrier tariff.
Read the full step-by-step walkthrough
- Declare the starting state
A sum starts at numeric zero. REDUCE returns the final state rather than the intermediate states produced by SCAN.
- Visit the eight record positions
SEQUENCE supplies 1 through 8. item is a position within each source range, not a quantity or worksheet row number.
- Test the current status
INDEX reads that position in D2:D9. Exact text comparison is case-insensitive here, but Posted followed by a space is excluded.
- Read paired quantity and rate
The same item position must select both numeric fields. INDEX(B2:B9,1) reads worksheet B2, because positions begin within the range.
- Round before adding
Read the paired quantity and rate, round the signed line to cents and then add it to total. Nonposted records contribute zero.
Things to watch
Including draft records
Only case-insensitive exact Posted qualifies; spaces are not silently removed.
Summing refunds as positive
Signed units affect the total in their original direction.
About this example
Bounded original teaching inputs and source-only assessment; actual browser calculation and spill checks are separate from native-client recalculation and edit/save.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.