Excel / VBA - the mod function

December 2016

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.

Related :

This document entitled « Excel / VBA - the mod function » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.