FLOOR in Zoho Sheet: Round Down to Multiples and Complete Packs
Round quantities down to multiples using FLOOR, with precise negative-mode rules and practical packaging examples.
Verification status: Official documentation referenced; example outcomes independently reasoned or arithmetically checked where stated. Not executed in Zoho Sheet. How formula examples are checked
Zoho Sheet FLOOR: round down to an allowed step size
Quick answer: =FLOOR(17.3;5) has the expected result 15, the greatest multiple of five that does not exceed 17.3. Use FLOOR when reporting complete cartons, full pallet layers, or the amount that fits within a limit without exceeding it. The positive-input answer is derived from Zoho’s official documentation and independently checked; it was not run in a native Zoho Sheet session.
FLOOR differs from ROUNDDOWN(number;digits): FLOOR’s second argument is an increment such as 5, 12 or 0.05, not a count of decimal places. With an increment of five, 17.3 becomes 15, not 17. Floor-rounding can lose part of a quantity by design, so keep the source value alongside the rounded result and calculate leftovers where appropriate.
Official syntax and arguments
=FLOOR(number; mult; [mode])
| Argument | Required? | Role | Example |
|---|---|---|---|
number |
Yes | Input to round to a multiple. | B2, 167, -11.3 |
mult |
Yes | Size of the multiple. Zoho’s FLOOR reference says this must be positive. | C2, 23, 0.05 |
mode |
No | Controls negative-input direction. With zero or omitted, Zoho documents rounding to a multiple greater than or equal to the negative value; with nonzero mode, rounding to a multiple below it. | 0, 1 |
The official Zoho FLOOR reference gives =FLOOR(11.3;2) → 10 and =FLOOR(167;23) → 161. Its published negative example uses mixed separators, =FLOOR(-11.3; 2,1), alongside a result of -12. Because the syntax specification uses semicolons, this guide writes the intended three arguments consistently as =FLOOR(-11.3;2;1), while marking native execution unverified. Never copy a malformed example without correcting the argument separation for your chosen locale.
The help article does not enumerate the function’s first supported Zoho release or every desktop/mobile version. Treat platform and older offline support as not established by the current web reference alone.
Practice worksheet and step-by-step example
Import the bundled math-multiples-practice.csv into A1 or type the following rows. A is the item ID, B the value to round, C the increment and D an exponent used by the related POWER article.
| Row | A: Item | B: Value | C: Increment |
|---|---|---|---|
| 2 | R01 | 17.3 | 5 |
| 3 | R02 | 11.3 | 2 |
| 4 | R03 | 167 | 23 |
| 5 | R04 | 4.42 | 0.05 |
| 6 | R05 | -11.3 | 2 |
| 7 | R06 | 30 | 12 |
| 8 | R07 | 0 | 5 |
| 9 | R08 | 24 | 6 |
- Type
Complete-multiple amountin E1. - In E2, type
=FLOOR(B2;C2). Expected result: 15. - Fill down through E5. Independently predicted results are 15, 10, 161 and 4.40.
- Use F2 for unallocated remainder:
=B2-E2. For 17.3 and five, that remainder is 2.3. In an inventory model with integer item counts, use whole numbers rather than assuming fractional units are always meaningful. - Check a value already at a multiple:
=FLOOR(B9;C9)returns 24 by the documented rule. No extra increment is subtracted.
At a price increment of $0.05, =FLOOR(4.42;0.05) has an expected result of 4.40. This is a deliberately downward pricing policy and must not be used where rounding down would violate a minimum charge or inventory requirement. Decimal presentation and binary floating-point effects near exact boundaries require independent native checks before financial deployment.
Negative-number mode: the counterintuitive part
With B6 = -11.3 and C6 = 2, applying Zoho’s published negative-mode description gives:
| Formula | Expected output | Why |
|---|---|---|
=FLOOR(B6;C6) |
-10 | Default rounds toward a greater multiple for negatives |
=FLOOR(B6;C6;0) |
-10 | Explicit default mode |
=FLOOR(B6;C6;1) |
-12 | Nonzero mode selects a lower multiple |
These predictions are consistent with Zoho’s prose and with its negative example’s reported -12 after separator correction. However, the example as printed in the help article is malformed, so the guide retains a native-test-needed flag. Do not rely on sign-related behaviour merely because Excel offers a function with the same name.
Advanced application: count full pallets and loose stock
Suppose a warehouse has 125 units and pallets hold 24 units. To count only stock that fits in complete pallets:
=FLOOR(125;24)
Expected packed quantity is 120, meaning 5 full pallets and 5 loose units. You can calculate each:
=FLOOR(125;24)/24
=125-FLOOR(125;24)
The expected outputs are 5 and 5. With input cells B2 = 125 and C2 = 24, replace the literals by B2 and C2; for example =B2-FLOOR(B2;C2). If quantities must be whole numbers and negative returns are possible, first validate the inputs. This workflow makes a critical business distinction: FLOOR is useful for identifying fully filled packs, while CEILING is appropriate for packs required to satisfy positive demand.
To flag unsold or unallocated stock, =IF(B2-FLOOR(B2;C2)>0;"Loose units";"Exact packs") is a proposed combined-function formula. For B2 = 125, C2 = 24 its independently predicted status is Loose units; it has not been executed in Zoho Sheet. The IF syntax is from Zoho’s documentation, and you should test custom locale separators natively.
Errors, edge cases and limits
| Situation | What to check |
|---|---|
#VALUE! |
An argument may be text, an unparsed localized number or a pasted label. Convert source columns to numbers. |
#NAME! |
Check spelling, recognized function name and named references. |
#REF! |
Inspect cell ranges after moving or deleting columns. |
| A negative value moved toward zero | Zoho’s documented omitted/zero mode uses a greater multiple for negative values. Set mode deliberately. |
A negative mult |
Zoho’s FLOOR argument definition requires a positive increment; use a positive magnitude. |
| Expected full-pack count differs from returned value | FLOOR gives rounded units, not how many packs. Divide by the pack size. |
| Rounding appears wrong at 4.40 or another decimal step | Check original precision and the cell format; reproduce a boundary case natively. |
The public FLOOR article includes a generic function-error table but does not specify every error code for zero increment, extremely large values or all coercion cases. Do not invent outputs for those cases. A zero-size pack has no operational meaning: validate C2>0 before division or rounding.
Choosing between related functions and other software
- FLOOR identifies the permitted multiple that does not exceed a positive input;
FLOOR(17.3;5)→ 15. - CEILING identifies a sufficient positive multiple;
CEILING(17.3;5)→ 20. - MROUND chooses the nearest multiple;
MROUND(17.3;5)→ 15. - INT rounds to an integer boundary of size one; its treatment of negative values differs from Zoho FLOOR’s documented default mode.
Microsoft’s legacy Excel FLOOR accepts two arguments and has different sign and negative-direction descriptions. Excel FLOOR.MATH accepts an optional mode, but its mode convention is not identical to Zoho FLOOR’s published prose. Excel’s comma-separated formulas are not a reliable copy-and-paste test of semicolon-separated Zoho examples. Google Sheets also has multiple floor-style functions; compare the specific function and its arguments, not just its English name.
Related proposed guides: CEILING, MROUND, INT, TRUNC, QUOTIENT. These URLs are editorial suggestions, not verified live links.
Official reference and verification
Verification status: Official documentation checked; example outcomes were independently reasoned or arithmetically checked where applicable. Not executed in Zoho Sheet.
- No genuine Zoho Sheet screenshots are included; this guide does not use mock application images.