Split a number into various factors (packs of 50, packs of 25 etc)

[scottwsms]

Hi All, I know this is possible because I'm sure I've learned how to do it in the past but I'm drawing a blank, so coming to you for help!

In a spreadsheet I'm building we need to provide a number of Earplugs (lets say 158). We can buy Earplugs in packs of 50 and packs of 25. Essentially i would like to input the Total number of earplugs needed in cell A3, and then cell B3 would calculate how many packs of 50 I would need, and cell C3 would calculate how many packs of 25 I would need.

In the example shown it would be 3 packs of 50, one pack of 25. I would also like it if the formula prioritized the packs of 50 first (buying in bulk is cheaper!).

Any help would be greatly appreciated (formula rather than VBA if possible)

Thanks

 

[scottwsms]

One idea i had was using ROUNDDOWN and ROUNDUP as follows:

A3 = 158
B3 (packs of 50) : =ROUNDDOWN(A3/50,0) ANSWER OF 3
C3 (Packs of 25) : =ROUNDUP((A3-(B3*50))/25,0) ANSWER OF 1

Not sure if that's the most efficient way but it does work. Any other alternatives?

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C
1
2
3	158	3	1
Main

Cell Formulas
Range	Formula
B3	B3	=INT(A3/50)
C3	C3	=ROUNDUP((A3-B3*50)/25,0)

 

[scottwsms]

How about

+Fluff 1.xlsm
	A	B	C
1
2
3	158	3	1
Main

Cell Formulas
Range	Formula
B3	B3	=INT(A3/50)
C3	C3	=ROUNDUP((A3-B3*50)/25,0)

Integer round down is nice for the first formula yeah, a little more compact. Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.