Excel / VBA - the mod function

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.
Published by aakai1056. Latest update on November 8, 2012 at 01:45 PM by Jeff.
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 (https://ccm.net/).
Send Email with Attachments Using an Excel Macro
Excel - Change date automatically