Oct 03 2019 04:58 PM - edited Oct 03 2019 05:17 PM
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...
Oct 03 2019 09:33 PM
SolutionIn the attached file, the formula in H2 is:
=INDEX(B1:F1,
MATCH(LOOKUP(2,1/(B2:F2>0),B1:F1),
B1:F1,0)+1)
Oct 03 2019 10:28 PM
Thanks @Twifoo - works perfectly, cheers.
Oct 03 2019 10:37 PM
You're very much welcome!
Oct 03 2019 09:33 PM
SolutionIn the attached file, the formula in H2 is:
=INDEX(B1:F1,
MATCH(LOOKUP(2,1/(B2:F2>0),B1:F1),
B1:F1,0)+1)