• 460K Members
• 10.9K Online
• 557K Conversations
SOLVED

New Contributor

Using index match offset to find first date that amounts drop to zero

Hi,

Trying to build a formula that returns the first date that a series of amounts ceases, ie drops to zero, using an index on date range, and attempting to match where amount is zero and amount of month prior is >0.

Importantly, the series of payments may not begin in first month, so no value in just finding the first zero amount...

My formula currently is CSE but returning #n/a:

{=INDEX(C1:F1,,MATCH(TRUE,(C2:F2=0)*(OFFSET(C2:F2,0,-1)>0),0))}

Cells B1..F1 are a range of 5 dates from 1 Jan to 1 May

Cells B2..F2 are amounts 0,0,500,500,0

 Month 1-Jan 1-Feb 1-Mar 1-Apr 1-May Drop off date PX \$ 0 0 500 500 0 #N/A

In the above case formula should be returning "1 May", as when series first dropped to zero.

Many thanks in advance...

3 Replies
Solution

Re: Using index match offset to find first date that amounts drop to zero

In the attached file, the formula in H2 is:

=INDEX(B1:F1,
MATCH(LOOKUP(2,1/(B2:F2>0),B1:F1),
B1:F1,0)+1)

Re: Using index match offset to find first date that amounts drop to zero

Thanks @Twifoo - works perfectly, cheers.

Re: Using index match offset to find first date that amounts drop to zero

You're very much welcome!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies