What funtion/s should do I need

Solved/Closed
Report
-
 BJ -
I'm working on tool for a user to input a qty of items, then calulate the number of cartons required.

The cartons come in 1, 5 & 20 unit packs.

If a user asks for 56, how do i calculate in excel/VBA to get the answer of 2 x 20, 3 x 5 & 1 x 1?

BJ

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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 for your help on this