SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-892591%22%20slang%3D%22en-US%22%3EUsing%20index%20match%20offset%20to%20find%20first%20date%20that%20amounts%20drop%20to%20zero%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-892591%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3ETrying%20to%20build%20a%20formula%20that%20returns%20the%20first%26nbsp%3B%3CEM%3Edate%3C%2FEM%3E%20that%20a%20series%20of%20amounts%26nbsp%3B%3CEM%3Eceases%3C%2FEM%3E%2C%20ie%20drops%20to%20zero%2C%20using%20an%20index%20on%20date%20range%2C%20and%20attempting%20to%20match%20where%20amount%20is%20zero%20and%20amount%20of%20month%20prior%20is%20%26gt%3B0.%3C%2FP%3E%3CP%3EImportantly%2C%20the%20series%20of%20payments%20may%20not%20begin%20in%20first%20month%2C%20so%20no%20value%20in%20just%20finding%20the%20first%20zero%20amount...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20formula%20currently%20is%20CSE%20but%20returning%20%23n%2Fa%3A%3C%2FP%3E%3CP%3E%7B%3DINDEX(C1%3AF1%2C%2CMATCH(TRUE%2C(C2%3AF2%3D0)*(OFFSET(C2%3AF2%2C0%2C-1)%26gt%3B0)%2C0))%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECells%20B1..F1%20are%20a%20range%20of%205%20dates%20from%201%20Jan%20to%201%20May%3C%2FP%3E%3CP%3ECells%20B2..F2%20are%20amounts%200%2C0%2C500%2C500%2C0%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3EMonth%3C%2FTD%3E%3CTD%3E1-Jan%3C%2FTD%3E%3CTD%3E1-Feb%3C%2FTD%3E%3CTD%3E1-Mar%3C%2FTD%3E%3CTD%3E1-Apr%3C%2FTD%3E%3CTD%3E1-May%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EDrop%20off%20date%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPX%20%24%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E500%3C%2FTD%3E%3CTD%3E500%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%23N%2FA%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20above%20case%20formula%20should%20be%20returning%20%221%20May%22%2C%20as%20when%20series%20first%20dropped%20to%20zero.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-892591%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-892730%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20index%20match%20offset%20to%20find%20first%20date%20that%20amounts%20drop%20to%20zero%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-892730%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F419820%22%20target%3D%22_blank%22%3E%40RobTech1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%2C%20the%20formula%20in%20H2%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DINDEX(B1%3AF1%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EMATCH(LOOKUP(2%2C1%2F(B2%3AF2%26gt%3B0)%2CB1%3AF1)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EB1%3AF1%2C0)%2B1)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-892792%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20index%20match%20offset%20to%20find%20first%20date%20that%20amounts%20drop%20to%20zero%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-892792%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B-%20works%20perfectly%2C%20cheers.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-892796%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20index%20match%20offset%20to%20find%20first%20date%20that%20amounts%20drop%20to%20zero%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-892796%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F419820%22%20target%3D%22_blank%22%3E%40RobTech1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20very%20much%20welcome!%3C%2FP%3E%3C%2FLINGO-BODY%3E
RobTech1
New Contributor

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

 

Month1-Jan1-Feb1-Mar1-Apr1-May Drop off date
PX $005005000 #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

@RobTech1 

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)

Thanks @Twifoo - works perfectly, cheers.

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