A few words of thanks would be greatly appreciated.

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.

A few words of thanks would be greatly appreciated.

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


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 (ccm.net).