The most widely used Office Software
, Excel, comes loaded with built in formulas
that help users to analyze and manipulate data according to the needs of the analysis. One such example in Excel is the mod function
. The user must specify the number and the divisor as the arguments of the mod function
to derive the remainder. One can combine multiple formulas
together to fit a certain equation and derive the desired result.
I'm working on a tool for a user to input a number of items, then calculate the number of cartons required.
- The cartons come in 1, 5 & 20 unit packs.
- If a user enters 56, then in Excel/VBA, I want to get the following answer:
2 x 20, 3 x 5 & 1 x 1?
Use mod function
Twenties = (56 - (56 Mod 20)) / 20
NextBox = 56 Mod 20
Fives = (NextBox - (NextBox Mod 5)) / 5
NextBox = NextBox Mod 5
Ones = NextBox
Thanks to rizvisa1 for this tip.
This document, titled « Excel / VBA - the mod function », is available under the Creative Commons
license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM