Michaels
Active Member
- Joined
- Apr 2, 2009
- Messages
- 404
Hi all !
I am still stuck with returns on FIFO basis. This is my current sales table:
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
Now I have tried SumProduct, Offset, and round many times, but couldn't come with the right formula. I tried it in Aggregate function but hard luck.
What I am looking for is, if the negative quantity is entered in Cell C4 (current value is -272), the formula in D5 (current value is -4226), it should do reverse sumproduct of Qty Sold and Avg Tranch Out Cost column matching the product.
If we enter -272, D5 should return -4226. How ?
( 62 x 16.19 ) + (116 x 15.51) + (94 x 15.14) = -4226
If we enter -150, it should return:
(62 x 16.19) + (88 x 15.51) = -2369 (rounded) based on product name in column B. So effectively, we are reversing FIFO cost.
I hope I have explained it correctly.
Thank you for your help.
Note: The Avg Tranche Out Cost is a helper column only.
I am still stuck with returns on FIFO basis. This is my current sales table:
Sell Date | Product Name | Qty Sold | FIFO COGS | Avg Tranch Out Cost |
09/03/2014 | Prod 1 | 94 | 1423.00 | 15.14 |
20/03/2014 | Prod 1 | 116 | 1799.00 | 15.51 |
29/03/2014 | Prod 1 | 62 | 1004.00 | 16.19 |
29/03/2014 | Prod 1 | -272 | -4226.00 |
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
Now I have tried SumProduct, Offset, and round many times, but couldn't come with the right formula. I tried it in Aggregate function but hard luck.
What I am looking for is, if the negative quantity is entered in Cell C4 (current value is -272), the formula in D5 (current value is -4226), it should do reverse sumproduct of Qty Sold and Avg Tranch Out Cost column matching the product.
If we enter -272, D5 should return -4226. How ?
( 62 x 16.19 ) + (116 x 15.51) + (94 x 15.14) = -4226
If we enter -150, it should return:
(62 x 16.19) + (88 x 15.51) = -2369 (rounded) based on product name in column B. So effectively, we are reversing FIFO cost.
I hope I have explained it correctly.
Thank you for your help.
Note: The Avg Tranche Out Cost is a helper column only.