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
- 10 & 10, I need 100% of each 10
- 5, 5, 5, 10, I need 100% of each 5 and 50% of the 10
I have looked at a nested if but it started to get complicated and difficult to read and debug.
Is there a good way to tackle this problem?
Thank you for your help.
With data in A2 and down, and the target amount in D2, enter the following formula in B2:
=IF(A2="", "", MIN($D$2-SUM(A$1:A1), A2)/A2)Format as a percentage, then fill down as far as you want.
Thank you, I can see how that works. My example was over simplified so I will now try and build your answer into my spreadsheet, I may have further questions but you've given me a great place to start.
Thank you for your help.
This example better explains what I am trying to do. Probably in a table.
Thank you for your help.
You've lost me completely. Why does a formula suddenly appear in row 6 for the first time? Why not in row 3, or in row 8?
What you have appears to be a FIFO calculation. The odd thing, though, is that you appear to be inferring the outputs (fuel used) from the future inputs (volume purchased). An explicit statement of outputs would be helpful, especially since your rules are difficult to follow.
I have attached a 356 file that uses a FIFO function but it is far from simple to follow.
Hi, thank you, I will take a look at your example.
I am trying to work the cost of the fuel used based on the volume purchased. Assuming the tank is always filled to capacity then the 20 litres I’ve just put in replaced 20 litres I purchased at a previous price. The 20 litres may be just the last fill-up or may need to include a number of previous fill-ups.
Does this mean I am trying to use a Last In First Out calculation?
Does this help?
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.
