WPS Spreadsheet · Practical projects · Intermediate
Compare Pack Prices on a Common Mass Basis in WPS Spreadsheet
Convert grams and kilograms, account for inner packs, and compare fictional prices without hiding invalid inputs.
Documentation-reviewed Windows procedure; arithmetic independently checked; native verification pending · Editorial standards
A larger pack can have a lower price per kilogram but a higher price at checkout. Build a worksheet that keeps those two measures separate. This example compares four invented offers for the same dry ingredient; it does not recommend a product or make a purchase.
Scope: ordinary cells and formulas in an already installed WPS Spreadsheet for Windows, using the basic free desktop editing route. No AI, premium template, account connection or paid feature is required. The vendor’s Windows function documentation is unversioned rather than a guarantee for a numbered build. Documentation was checked on 21 October 2026; no installed WPS build, native calculation, screenshot or cross-platform parity was tested. Formula examples use English names, decimal points and commas; adapt separators to your locale.
Define exactly what one price buys
Start an empty workbook. Enter this table in A1:E5. All prices use the same fictional currency. B is the price of the whole offer, C counts inner packs included in that price, and D is the mass of one inner pack.
| A: Offer | B: Whole-offer price | C: Inner packs | D: Mass per inner pack | E: Unit |
|---|---|---|---|---|
| Oat A | 2.40 | 1 | 400 | g |
| Oat B | 3.90 | 1 | 0.75 | kg |
| Oat C | 5.70 | 3 | 350 | g |
| Oat D | 6.75 | 1 | 1.25 | kg |
Do not enter 400 g in a numeric cell. Put the number in D and its unit in E. Count only the contents covered by B: multiplying by three when the displayed price is actually for one inner pack would understate unit price threefold.
This worksheet deliberately handles mass only. It does not convert litres to kilograms, count tablets, equate different concentrations, or compare drained weight with gross package weight. Keep different products and bases in separate comparisons. Delivery, discounts, waste and taxes are not modeled.
Expose bad inputs before dividing
Enter these headings in F1:K1: Price check, Pack check, Mass check, Grams per unit, Total grams, Price per kg.
Enter each formula in its specified row-2 cell, beginning with =, and press Enter. Copy F2:K2 down through row 5 using the fill handle; inspect the row references in the last row.
| Cell | Formula |
|---|---|
| F2 | =IF(ISNUMBER(B2),IF(B2>=0,"OK","CHECK PRICE"),"CHECK PRICE") |
| G2 | =IF(ISNUMBER(C2),IF(C2>0,IF(C2=INT(C2),"OK","CHECK PACKS"),"CHECK PACKS"),"CHECK PACKS") |
| H2 | =IF(ISNUMBER(D2),IF(D2>0,"OK","CHECK MASS"),"CHECK MASS") |
| I2 | =IF(E2="g",1,IF(E2="kg",1000,"CHECK UNIT")) |
| J2 | =IF(F2<>"OK","CHECK INPUT",IF(G2<>"OK","CHECK INPUT",IF(H2<>"OK","CHECK INPUT",IF(ISNUMBER(I2),C2*D2*I2,"CHECK INPUT")))) |
| K2 | =IF(ISNUMBER(J2),B2/J2*1000,"CHECK INPUT") |
IF chooses between the success and warning branches. ISNUMBER checks type before arithmetic. INT lets the pack check reject fractional counts. The nested checks are intentional: a blank, negative or text mass must not become a plausible price or divide-by-zero result.
Zero price is allowed for an explicitly free offer, but a missing price is not zero. Packs must be positive whole numbers and mass must be positive. Only the unit labels g and kg are recognized; do not silently guess what oz, ml or a blank means. These checks do not verify that a plausible number was copied correctly from a label.
Reconcile the normalized results
F:H should all show OK. I2:I5 should show 1, 1000, 1, 1000. The independent arithmetic is:
| Offer | Total grams in J | Price per kg in K |
|---|---|---|
| Oat A | 400 | 6 |
| Oat B | 750 | 5.2 |
| Oat C | 1050 | 5.428571… |
| Oat D | 1250 | 5.4 |
For example, Oat C supplies 3 × 350 = 1050 g, so 5.70 ÷ 1050 × 1000 = 5.428571… per kg. In this fixture Oat B has the lowest price per kg, while Oat A requires the lowest whole-offer payment. Neither observation establishes which quantity someone needs.
Show enough decimal places to distinguish Oat C from Oat D. Keep the full formula result for comparison rather than typing the displayed rounded number over it. Two-decimal formatting would show 5.43 and 5.40; it changes presentation, not the underlying ratios. Do not sum unit prices or average them to obtain a combined price per kg. A combined rate would divide combined cost by combined mass.
Change one assumption and test the boundaries
Restore each input after its individual test so changes do not accumulate.
- Change B4 from 5.70 to 5.25. J4 stays 1050; K4 becomes 5. Restore 5.70.
- Change C4 from 3 to 2. J4 becomes 700 and K4 becomes 8.142857…. Restore 3. This checks the inner-pack multiplier, not just price division.
- Set D3 to 0, then to -0.75, then to the text
unknown. Each should produce CHECK MASS in H3 and CHECK INPUT in J3:K3. Restore numeric 0.75. - Set C4 to 2.5. G4 should show CHECK PACKS. Clear C4 entirely and check the same warning. Restore 3.
- Replace E3 with ml. I3 should show CHECK UNIT and J3:K3 CHECK INPUT. Restore kg.
- Set B2 to 0. F2 stays OK and K2 becomes 0. Clear B2 instead: F2 should show CHECK PRICE. Restore 2.40.
- As a deliberate plausible-input error, change E3 from kg to g while D3 remains 0.75. All input checks pass, but K3 becomes 5200. The checks cannot know that the package was 750 g. Restore kg and reconcile against the source label.
Keep a reusable checked copy
Save the workbook locally as Pack-mass-practice.xlsx. Reopen it and verify the four initial masses and rates above, including Oat C’s unrounded fraction. These are acceptance steps for the reader, not a claim that the author ran WPS.
For a new offer, add one complete row and copy all six formula columns, then independently check that row’s mass and price. A number pasted over K can look reasonable but no longer update; select it and confirm the formula bar still begins with =. For general copying and saving help, use relative references and save and reopen a workbook.
Troubleshooting
- A numeric-looking price is rejected: inspect whether it is text, contains a currency label, or uses the wrong decimal separator. Correct the input deliberately; changing display format alone is not a reliable text-to-number conversion.
- The rate is 1000 times too large or small: inspect E and I first. Do not repair the result by adding an unexplained factor to K.
- Only the first offer updates: inspect the copied row references. Row 5 must use B5:J5, not row 2.
- An invalid row still shows a number: check whether J or K was overwritten, or whether an old formula was pasted as a value. Restore the stated formulas before interpreting the comparison.
- Formula text is displayed: confirm the initial equals sign and the cell’s text/general state; see formula text troubleshooting.
Official references
- WPS IF syntax and conditional results.
- WPS ISNUMBER for Windows.
- WPS INT for Windows and fill-down and reference checks.
The offer data, conversion design and expected results are original teaching material. Sources support function behavior and entry/copying mechanics, not real-world product equivalence or a purchasing decision.