A mysterious if function to return a specific balance?
 

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" wrote:


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 my
transactions, that calculates the remaining sold to be consumed from
the first Debit, but if the first debit was totally consumed, it shall
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 D3
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 totally
(and definitely A2 value was totally consumed since 30 out of the 40 of
B6 cleared the first 100, and so on ..


Thank you for your support




--
hankach

A mysterious if function to return a specific balance?
 

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 my
transactions, that calculates the remaining sold to be consumed from
the first Debit, but if the first debit was totally consumed, it shall
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 D3
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 totally
(and definitely A2 value was totally consumed since 30 out of the 40 of
B6 cleared the first 100, and so on ..


Thank you for your support




--
hankach

A mysterious if function to return a specific balance?
 

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 out
method.(what enters first ,leaves out the first ) while i need the
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 have
80 to consume from the first debit which is 100 ; in the E3 Cell it
reads "A2" which means that the first debit value we refer to is of
cell A2.
Then after crediting 50 in B4, D4 shows that we still have 30 to
consume from the first debit which is 100 and A2 is the debit value we
refer to, and so on until in D6 , we see that by crediting 40 we
already consumed the remaining 30 of the first debit (100) and also
consumed 10 of the second debit (50) and by that the answer is 40 which
means that now we have 40 to consume of the second debit (50) , while
the cell E6 return A5 which refers to the debit (50) and which the sold
refers to.

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

Thank you so much for your support !




--
hankach