Find last date running total equalled zero

[RandomTandem]

If I have a list of dates (in chronological order) in Column A and a list of stock purchases or sales in column B. Is there a formula which will help me find the last date where the running total was equal to zero prior to a date I put in cell C1?

In the below example, If I put 13th Aug in cell C1, it would tell me 12th Aug but if I had 15th Aug in cell C1, it would put 14th Aug

Date	Purchase/Sale Amount
10th Aug	+5
11th Aug	+10
12th Aug	-15
13th Aug	+80
14th Aug	-80
15th Aug	10

<tbody>
</tbody>

I would normally use a helper column to achieve this but I cannot do it in this example.

Thank you.

 

[Aladin Akyurek]

In c2 control+shift+enter, not just enter:

=MAX(IF($A$2:$A$7<=C1,IF(SUBTOTAL(9,OFFSET($B$2,0,0,ROW($B$2:$B$7)-ROW($B$2)+1))=0,$A$2:$A$7)))

 

[RandomTandem]

Genius - thank you very much!

 

[RandomTandem]

Sorry: one more question

If I have two different brokerage accounts, named A and B, which is detailed in cells D2 to D7, how do I modify the formula to only do the running totals for account A?

 

[RandomTandem]

I tried to use an AND function in the initial IF statement but it is returning a zero - any help would be greatly appreciated.

=MAX(IF(AND($A$2:$A$7<=C1,$D$2:$D$7="A"),IF(SUBTOTAL(9,OFFSET($B$2,0,0,ROW($B$2:$B$7)-ROW($B$2)+1))=0,$A$2:$A$7)))

 

[Aladin Akyurek]

I tried to use an AND function in the initial IF statement but it is returning a zero - any help would be greatly appreciated.

=MAX(IF(AND($A$2:$A$7<=C1,$D$2:$D$7="A"),IF(SUBTOTAL(9,OFFSET($B$2,0,0,ROW($B$2:$B$7)-ROW($B$2)+1))=0,$A$2:$A$7)))

AND cannot be called upon in such formula in order to get an array result for AND cannot but deliver a single result.

I think you need to construct an additional range which you can use for farther evaluations... If you want to take this turn, try to post a representative but concise sample.

 

[RandomTandem]

Imgur: The most awesome images on the Internet

AND cannot be called upon in such formula in order to get an array result for AND cannot but deliver a single result.

I think you need to construct an additional range which you can use for farther evaluations... If you want to take this turn, try to post a representative but concise sample.

Thank you...I have uploaded an image to imgur (hopefully it will work)

In the attached picture, columns D and E show a continuous running total, the perfect solution would not have these helper columns.

For Account A, I'd be looking to return 16-Jul-17 for any dates prior to 23-Jul-17, then return 23-Jul-17 for any dates thereafter.

For Account B, I'd be looking to return 0 for any dates prior to 19-Jul-17, then return 19-Jul-17 for any dates between 19-Jul-17 and 23-Jul-17 and then 24-Jul-17 for any date after and including 24-Jul-17.

Many thanks,

 

[Aladin Akyurek]

Imgur? I'm sorry I have no intention in retyping what an image shows into Excel for experimentation or testing.

 

[RandomTandem]

Imgur? I'm sorry I have no intention in retyping what an image shows into Excel for experimentation or testing.

OK sorry - I don't know how to paste tables into this forum - I will type it...

Account	Date	Buy/Sell	Account A	Account B
B	16-Jul-17	200	0	200
A	17-Jul-17	50	50	200
A	18-Jul-17	10	60	200
B	19-Jul-17	-200	60	0
A	20-Jul-17	-40	20	0
B	21-Jul-17	40	20	40
A	22-Jul-17	50	70	40
A	23-Jul-17	-70	0	40
B	24-Jul-17	-40	0	0

<tbody>
</tbody>

 

[Aladin Akyurek]

Are the values below A and B calculated by means of a formula? if so, care to post that formula?