How do I add or sum until?

[iwaddo]

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. 

[#2]

@iwaddo 

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.

[#3]

@HansVogelaar 

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. 

[#4]

@HansVogelaar 

This example better explains what I am trying to do. Probably in a table.

Thank you for your help. 

[#5]

@iwaddo 

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?

[#6]

@HansVogelaar 

Sorry, my example is not clear. I do want a formula in every row as I add a new row. I just did the bottom few to try and highlight what I was trying to do.

In simple terms I want to calculate the cost of fuel just used.

Thank for trying to help

[#7]

@iwaddo 

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.

[#8]

@PeterBartholomew1 

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?

[#9]

@iwaddo 

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.

[#10]

@PeterBartholomew1 

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. 

[#11]

@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...

[#12]

@djclements 

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. 

[#13]

@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