Forum Discussion
How do I add or sum until?
If my input is 20, I want to add back up a column sequential until I've reached >=20 Examples, if the column is - 5, 10, & 12, I need 100% of the 5, 100% of the 10 & only 41.66% of the 12 - 1...
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
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