I haven't been able to find a solution to my current problem so hopefully my request will help others here as well.

I have data arranged as follows(pls see screenshot attached, I am not yet able to insert images):

Date:      1/1 1/2 1/3 1/4 1/5 
Quantity:  0   1   0   0   0   Desired Result: 1/3  
Quantity:  1   1   1   0   0   Desired Result: 1/4  
Quantity:  1   0   1   0   0   Desired Result: 1/4  
Quantity:  0   0   0   0   0   Desired Result: na  
Quantity:  1   1   1   1   1   Desired Result: na

I have ~2k lines of quantities. I need a formula which would allow me to return the date at which the quantity falls to 0. In the example above, it would be the date in the "Desired Result" column

I know I need an index formula to return the date, but I just can't seem to get the criteria right.

EDIT: image of spreadsheet and desired result attached Screenshot of Table

  • Can you expand your sample data and expected output a little. What happens on rows with 0 0 0, 0 0 1 or 3 2 1? Also, you don't explain why your current example should return 1/3 and not 1/1. It would also be helpful to see what you have tried – cybernetic.nomad May 22 '19 at 15:39
  • Good questions, thank you. Was just trying to keep it as simple as possible. Essentially, I'm looking for the date an account stopped holding inventory. So if it started with 0,0,1, then it's presumed to still be holding inventory. I'm trying to zero in on accounts have closed out. So in my example, on 1/1 the account held nothing, then on 1/2 held 6, and on 1/3 closed out. Does that help? – Hodor May 22 '19 at 15:40
  • You should edit your question to add information to it. I also suspect you may have more than 3 columns, in which case, it would be useful to know what the expected result would be for something like 1 0 1 0 2 0 – cybernetic.nomad May 22 '19 at 15:43
  • Expected result for 1 0 1 0 2 0 would be 1/6 (the first date after the final date account held inventory) – Hodor May 22 '19 at 15:45
  • @cybernetic.nomad, I updated my table and provided a screenshot of the file with the desired result. I hope that is more clear. Thank you for the feedback thus far! – Hodor May 22 '19 at 16:09

In D2 enter:


and copy downward:

enter image description here


To return the last instance of 0, use this formula instead:

| improve this answer | |
  • Gary, thank you for the suggestion. I don't believe this formula works because it still pulls the first date we see a 0 (row 22). I need to pull the last date that a 0 appears, provided that the date before had a quantity. - Does that make sense? I'm going to update my original post to be more clear. – Hodor May 22 '19 at 15:56
  • @Hodor See my EDIT#1 – Gary's Student May 22 '19 at 16:11
  • Gary, Thanks for the update. I think my failure to explain the situation clearly has struck again. I actually have many month's worth of dates, so typing a sequential formula wouldn't work. Moreover, I'm not sure that formula would work if the sequence is 0,1,1,0,0 or 1,0,1,0,0 (in both chases result should be 1/4). – Hodor May 22 '19 at 16:19

Found a solution on another forum for anyone interested:

=IFERROR(1/(1/INDEX($2:$2, AGGREGATE(14,6,COLUMN($C3:$G3)/($C3:$G3<>0),1)+1)), "na")

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.