Sorting a cell amount into specific labelled amount columns

[mcdooglie]

Hi guys,

I'm a relative newbie to excel, i.e. can do formulas, but not much to do with VBA/macros etc so there may be a method to do it that way but I may need walking through.

A brief overview of what i want this excel sheet to do. I'm doing a project on merchandise suppliers (we have 6), and they have supplied us with prices for various bulk purchases (i.e a pallet of 100, 500, 2000 etc as examples). We can only buy from the bulk prices and not individually so if i require 2350 items, ill need 1 x 2000 and 4 x 100 which will give me 2400 as I cannot have less than what i require.

I want to sort various amounts found in cell A6-A11, into columns labelled from B5-G5 that carry various values.
For example if I have an amount of 2000, and I have columns in order from left to right of 100, 500, 1000 and 1500.
I want it to attempt to sort itself from the highest value to the lowest value. So in that case it will leave a mark or the number "1" to represent the amount of multiples in the column as 1 x 1500, and 1 x 500.

Once I have that designed I need it to deal with slightly more difficult numbers. For example if the number in cell A6 was 2250, and I have columns in order from left to right of 100, 500, 1000 and 1500 as before.

It would sort from the highest amount to lowest in 1 x 1500, 1 x 500, and 2 x 100, but the remainder of 50 would not be allocated.
In this case I need the remainder to be rounded up so that it is counted as another 1 x 100, giving me a total of 2300, from my original 2250 value entered.

Please see my example I have put together. It is not capable however to run the remainders into the COUNT section I have set up.
Any help is appreciated.

[DonkeyOte]

As a starting point, based on your sample file & values, how about:

Please Login or Register  to view this content.

[mcdooglie]

Thanks DonkeyOte, works a treat!

Next I have to try and see how to see if an amount is cheaper to buy as a bigger bulk amount, or the exact amount required.

I.e I am required to buy 45500 items, i could buy 2 x 20000, 0 x 10000, 1 x 5000 and a 1 x 500 to get my exact amount but based on the prices, am i better off rounding to 50000 items and buying 2 x 20000, and 1 x 10000.

Not sure if you'll understand what im saying or not, but thought id throw it out there! :-P

I have attached an example below.

[mcdooglie]

As a starting point, based on your sample file & values, how about:

Please Login or Register  to view this content.

One problem I have encountered with the formula is when it comes to sorting into bulk numbers like 144, and 288 it doesn't work to well.

See attached example.

[DonkeyOte]

Using the latest example:

Please Login or Register  to view this content.

The above is based on the earlier methodology regards best pricing.

However, based on:

I am required to buy 45500 items, i could buy 2 x 20000, 1 x 5000 and a 1 x 500 to get my exact amount but based on the prices, am i better off rounding to 50000 items and buying 2 x 20000, and 1 x 10000.

edit:

it really depends on the pricing - your table in the latest sample implies smaller units are more expensive which would be an odd scenario I think, no ?
we would expect smallest to be cheapest but with marginal price reducing due to economies of scale, eg:

Please Login or Register  to view this content.

so 50 item is cheaper than 100 item but were you looking to cater for say 75 it would always be cheaper to buy 1 unit of 100 than 2 units of 50

I've re-written this above five times now ... going around in circles...

I think the marginal reduction in per unit pricing will also have a bearing on the optimal combination, consider:

Please Login or Register  to view this content.

If you want to buy 2700 units it would be cheaper to buy 1 x 500 + 1 x 2500 [0.5 + 1.4] than any other combination.

However, if we reduce the margin between prices further such that:

Please Login or Register  to view this content.

then the optimal bundle changes to 1 x 5000

I think this might warrant Solver (not something I have any skill with myself)

[mcdooglie]

DonkeyOte you are awesome, thanks for spending the time to develop a solution and reply, greatly appreciated.

I'm researching the Solver now to see if I can develop something, will let you know if its a success :-P