Audit case-pack multiples and whole-percentage inputs with MOD and ROUND

Separate integer quantity checks from decimal tolerance

Require both quantity and pack size to be positive whole numbers before using MOD. Guard with IF so text or a zero pack size returns FALSE without attempting an invalid remainder calculation. This example limits both integers to 1e12 to keep them comfortably within ordinary spreadsheet numeric precision.

For percentage input, 0.07 represents 7% and 0.075 represents 7.5%. Test the distance between the value multiplied by 100 and its nearest integer. Decimal arithmetic can leave tiny residuals, but spreadsheet engines may handle those residuals differently; there is no promise of identical bit-level behavior.

The tolerance here is strictly less than 1e-6 percentage points, equivalent to 1e-8 in fractional input units. It deliberately accepts values extremely close to a whole percentage point. The separate range check still requires 0<=value<=1. These formulas audit cells; they do not themselves install an application input restriction.

Require positive exact multiple of pack size D2

A valid quantity must be an integer positive multiple of a positive integer pack size, with both no greater than 1e12. Blanks, text, zero and fractional pack sizes are rejected.

=IF(AND(ISNUMBER(A2),ISNUMBER($D$2)),IF(AND(A2>0,$D$2>0,A2<=1000000000000,$D$2<=1000000000000,A2=INT(A2),$D$2=INT($D$2)),MOD(A2,$D$2)=0,FALSE),FALSE)

Check whole-percentage increments within the chosen tolerance

Accepts 0.00, 0.07, 0.25, 1.00 while rejecting fractional percentages like 0.075 or out-of-range 1.20.

=IF(ISNUMBER(B2),AND(ISNUMBER(B2), B2 >= 0, B2 <= 1, ABS(B2 * 100 - ROUND(B2 * 100, 0)) < 0.000001),FALSE)

Further reading

• Microsoft Support — MOD function