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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Purchase/Sale Amount[/TD]
[/TR]
[TR]
[TD]10th Aug[/TD]
[TD]+5[/TD]
[/TR]
[TR]
[TD]11th Aug[/TD]
[TD]+10[/TD]
[/TR]
[TR]
[TD]12th Aug[/TD]
[TD]-15[/TD]
[/TR]
[TR]
[TD]13th Aug[/TD]
[TD]+80[/TD]
[/TR]
[TR]
[TD]14th Aug[/TD]
[TD]-80[/TD]
[/TR]
[TR]
[TD]15th Aug[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Account[/TD]
[TD]Date[/TD]
[TD]Buy/Sell[/TD]
[TD]Account A[/TD]
[TD]Account B[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]16-Jul-17[/TD]
[TD]200[/TD]
[TD]0[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]17-Jul-17[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]18-Jul-17[/TD]
[TD]10[/TD]
[TD]60[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]19-Jul-17[/TD]
[TD]-200[/TD]
[TD]60[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]20-Jul-17[/TD]
[TD]-40[/TD]
[TD]20[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]21-Jul-17[/TD]
[TD]40[/TD]
[TD]20[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]22-Jul-17[/TD]
[TD]50[/TD]
[TD]70[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]23-Jul-17[/TD]
[TD]-70[/TD]
[TD]0[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]24-Jul-17[/TD]
[TD]-40[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

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