How to calculate money needed to buy n cheapest items?

[Contiki]

Hi everyone,

let's say I have a price list of multiple vendors who sell an item I want to buy. Each one told me their price per item and the amount they have in stock. So the example may look like this.

Column A containing the price per item
$1,17
$1,21
$1,39
$1,40

And column B containing the amount each vendor has on hand:
50
60
20
15

How do I calculate the money needed to buy the cheapest 120 items off of as many vendors as needed? The amount can, of course, vary. That's just the example. With a required amount of 120 items I'd expect the result to be $145. To get the cheapest 120 items I'd have to buy all of vendor number one's 50 items (for $58,50), also all of number two's 60 items (totalling $72,60) and 10 items of number three's stock (at $13,90 for a total of $145).

I can't for the life of me figure out a simple way to do this.

Attached to the post you'll find an example workbook that does the trick, however, it requires multiple cells and the fomulas are unwieldy. Cell E1 is where you can play around with the amount you want to acquire. I refuse to believe that this is the best possible solution :) Maybe some of you fine folks can point out where I can condense and simplify the calculations.

I'd galdly appreciate any help!

PS: I created the file with a German Excel, meaning for me it uses German function names. Don't know if this is converted on the fly when it's opened with an English Excel. Please let me know if any issues arise.

[Jakobshavn]

You need a different, cummulative price table:
In A1 enter 1.17
In A2 enter =A1+1.17 and copy down thru A50
In A51 enter =A50+1.21 and copy down thru A110
In A111 enter =A110+1.39 and copy down thru A130
In A131 enter =A130+1.4 and copy down thru A145

Now if you want to buy 99 items, enter 99 in B1 and in C1 enter:
=INDIRECT("A" & B1) to display 117.79

[shg]

One way:

Please Login or Register  to view this content.

In C2 and copy down,

=MIN($C$2 - SUM(C$3:C3), B4)

[Contiki]

Thanks for all your answers! I'll stick with shg's option, that's the most convenient for me. Way better than what I had so far. I'll keep monitoring the thread in case someone actually comes up with a one-cell solution for such a formula. Until then shg's version will do nicely. I've marked the thread as solved. Thanks all!

[daddylonglegs]

It might be easier to stick with a helper column approach, you can do this with a single formula but it's not simple, try this "array formula"

=SUM(SMALL(IF(B2:B5>=TRANSPOSE(ROW(INDIRECT("1:"&MAX(B2:B5)))),A2:A5),ROW(INDIRECT("1:"&E1))))

confirmed with CTRL+SHIFT+ENTER