A mysterious if function to return a specific balance?

[Teethless mama]

In D3: =IF(A3="",C3,"")
In E3: =IF(D3<>"",LOOKUP(10^10,$A$2:A3),"")

Select D3:E3 and copy down as far as needed

 

[hankach]

Hello everyone,

If
A1=Debit; B1= Credit; C1= Sold
A2=100; A5=50; A7=10
B3= 20; B4= 50; B6= 40; B8=40
C2=A2; C3=C2+A3-B3; C4=C3+A4-B4; C5=C4+A5-B5; C6= C5+A6-B6
C7=C6+A7-B7

I need:

1) A formula in D3 and to be able to copy it down all along m
transactions, that calculates the remaining sold to be consumed fro
the first Debit, but if the first debit was totally consumed, it shal
diminish the difference from the second debit entry and so on ..

2) A formula in E3 to return the cell reference of the Debit that D
refers to.


Conclusion:

Given the data ahead the results should come as follows:
D3= 80 E3=A2 means there still 80 from A2 value to consume it totally.
D4= 30 E4=A2 means there still 30 from A2 value to consume it totally.
D6= 40 E6=A5 means there still 40 from A5 value to consume it totall
(and definitely A2 value was totally consumed since 30 out of the 40 o
B6 cleared the first 100, and so on ..


Thank you for your suppor

 

[hankach]

Thank you much for your prompt reply.
I tried to apply your formula, but the answers are not the same .

The answers should be as follows as working with the first in first ou
method.(what enters first ,leaves out the first ) while i need th
remaining sold each time :
D3= 80 E3= A2
D4=30 E4= A2
D5=30 E5= A2
D6=40 E6= A5
D7=40 E7= A5
D8=10 E8= A7

You can see that after crediting 20 in B3, D3 shows that we still hav
80 to consume from the first debit which is 100 ; in the E3 Cell i
reads "A2" which means that the first debit value we refer to is o
cell A2.
Then after crediting 50 in B4, D4 shows that we still have 30 t
consume from the first debit which is 100 and A2 is the debit value w
refer to, and so on until in D6 , we see that by crediting 40 w
already consumed the remaining 30 of the first debit (100) and als
consumed 10 of the second debit (50) and by that the answer is 40 whic
means that now we have 40 to consume of the second debit (50) , whil
the cell E6 return A5 which refers to the debit (50) and which the sol
refers to.

I can mail the sheet if that may clarify a little bit.

Thank you so much for your support