Can IF statements be used to calculate the most efficent way of ordering items-CodePudding

I have been trying to update a spreadsheet we currently using to show the most efficient way of ordering items. Items can be ordered in packs of 1, 5, 10 or 20. Can if statements be used to produce a table to show the best way of ordering these?

E.G: 18 - made of 1 x pack of 10; 1 x pack of 5; 3 x pack of 1

Been trying to use IF statements, but as the numbers get larger: more errors seems to creep in. I'm certain there is a simple solution i am missing at the moment.

CodePudding user response：

A quick go at this, but I added some prices:

But you may want to consider how you could get the result of ordering 20 and having 2 spare etc...

So, set that up like this, but you nay want to fine tune to control your situation more precisely:

CodePudding user response：

Packaging

You can handle this easily with INT and MOD.

=INT($A3/B$2)
=INT(MOD($A3,B$2)/C$2)

It was easy when the packaging was convenient. If it isn't, instead of MOD, you could use SUMPRODUCT.

=INT($A3/B$2)
=INT(($A3-SUMPRODUCT($B$2:B$2,$B3:B3))/C$2)