$ Total a Quantity from Lowest to Highest Price

Hello everyone. I am really stumped on this problem and I am not even sure if a macro is needed to solve it. Sure looks like it though.

Please see attached.

In sheet Problem 1, there is a Current Stock table and a Sale table. The highlighted cells, J6:J9 will initially be empty and this is what I would like to populate.

The Total in column J for each Item should be a dollar amount such that it equals the sum of [the lowest price of that item in the Current Stock table times the available quantity at that price]

Therefore, item A in cell H6 with a quantity of 3 will have a dollar total (2 x $0 + 1 x $1). Since there were only 2 units at $0 each in the current stock table, there is no available stock at this price anymore, while there remains 6 left of A priced at $1.

Thus, item A in cell H7 with a quantity of 2 will have a dollar total of (2 x $ 1). Now there are only 4 left of item A priced at $1 each in the Current Stock table.

As such, item A in cell H8 with a quantity of 9 will have a dollar total of (4 x $1 + 2 x $4 + 3 x $5).

And so on and so forth for the rest of the items.


In sheet Problem 2, the requirement is similar, but this time, there is a Type condition in the sale, which restricts the pricing of the item at the specified type.



I hope that made sense. I am having a really hard time figuring out how to do this. Any help/guidance would be very much appreciated.

Hi,

Probably this is what you need :

Please Login or Register  to view this content.

Regards

You are welcome uberathlete, thanks for the feedback.


Regards