walkingdead4435
New Member
- Joined
- Dec 26, 2013
- Messages
- 4
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 below.
<tbody>
</tbody>
In this sheet there is a Current Stock table and a Sale table. The cells J3:J6 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 of 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 another sheet below, 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.
<tbody>
</tbody>
I hope that made sense. I have am having a really hard time figuring out how to do this. Any help/guidance would be very much appreciated.
Please see below.
| A | B | C | D | E | F | G | H | I | J | K | |
| 1 | Current Stock | Sale | |||||||||
| 2 | Item | Type | Qty | Price | Total | Item | Qty | Total | Calculation: | ||
| 3 | A | Good | 6 | $5.00 | $30.00 | A | 3 | $1.00 | =2@$0 + 1@$1 | ||
| 4 | A | Good | 2 | $4.00 | $8.00 | A | 2 | $2.00 | =2@$1 | ||
| 5 | A | Bad | 2 | $0.00 | $0.00 | A | 9 | $27.00 | =4@$1 + 2@$4 + 3@$5 | ||
| 6 | A | Bad | 7 | $1.00 | $7.00 | B | 7 | $40.00 | =2@$0 +5@$8 | ||
| 7 | B | Good | 8 | $8.00 | $64.00 | ||||||
| 8 | B | Bad | 2 | $0.00 | $0.00 |
<tbody>
</tbody>
In this sheet there is a Current Stock table and a Sale table. The cells J3:J6 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 of 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 another sheet below, 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.
| A | B | C | D | E | F | G | H | I | J | K | ||
| 1 | Current Stock | Sale | ||||||||||
| 2 | Item | Type | Qty | Price | Total | Item | Type | Qty | Total | Calculation: | ||
| 3 | A | Good | 6 | $5.00 | $30.00 | A | Bad | 3 | $1.00 | =2@$0 + 1@$1 | ||
| 4 | A | Good | 2 | $4.00 | $8.00 | A | Good | 2 | $8.00 | =2@$4 | ||
| 5 | A | Bad | 2 | $0.00 | $0.00 | B | Bad | 2 | $0.00 | =2@$0 | ||
| 6 | A | Bad | 7 | $1.00 | $7.00 | |||||||
| 7 | B | Good | 8 | $8.00 | $64.00 | |||||||
| 8 | B | Bad | 2 | $0.00 | $0.00 |
<tbody>
</tbody>
I hope that made sense. I have am having a really hard time figuring out how to do this. Any help/guidance would be very much appreciated.
