Forum Discussion
How do I add or sum until?
It does appear that you are applying LIFO accounting to your task. Assuming you never run the tank dry, your valuation will always include the first few litres priced at the original cost per litre. I have never had cause to implement a LIFO calculation. Looking it up, I see that it is a banned accounting practice in all economies other than the United States! To think, I would never have known that had it not been for your question.
I doubt my little home project will get me into much trouble 🙂
I just need to work out how to do it.
Thank you for your help.
iwaddo Since it's for personal use, you could try the following formula in cell E3 to return the desired results:
=MAP(A3:A8, B3:B8, C3:C8, LAMBDA(seq,vol,cos, LET( by_arr, DROP(A2:seq, -1), total_vol, SCAN(0, SORTBY(DROP(B2:vol, -1), by_arr, -1), LAMBDA(a,v, a + v)), unit_cost, SORTBY(DROP(C2:cos, -1), by_arr, -1), used_vol, SORT(VSTACK(vol, total_vol)) - VSTACK(0, total_vol), SUMPRODUCT(DROP(used_vol, -1), unit_cost))) )Example Results
However, I should point out that the logic used here is flawed (it's not an accurate LIFO method). As you can see in your original screenshot, it will "double dip" from the wrong transaction line(s) whenever the volume purchased is greater than the previous row(s):
Original Example
Alternatively, you could use a weighted average method, which is a common accounting practice:
=MAP(B3:B8, D3:D8, LAMBDA(vol,cos, SUM(D2:cos) / SUM(B2:vol) * vol))Please see the attached workbook, if needed...
- djclements
This works perfectly and I can apply your example to my model in Excel.
As a further challenge is there a simpler approach that perhaps uses helper columns or interim calculation? Something more portable?
I tend to try and use Numbers in my Apple world, though I could use Excel and your example does work on my iPad.
It maybe that the weighted average approach is my simplest way forward.
Thank you very much for your help Wow, thank you. I've downloaded your example and will try work through it to see how to apply it to my model.
Thank you for your help.
