Excel / VBA - the mod function

Ask a question
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.


Issue


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.
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team

Related