I have a table with inventory quantities listed by date (left to right), and would like a formula that will return the cell where the last zero value appears. Referencing this result will allow me to complete a formula that shows "In stock since..." by indexing the header cell for that column (offset by 1 column, to the first cell >1 after the last zero.)
I don't want to use an array, nor a macro or VBScript.
This is part of a longer formula where I look for all zero values in the row ("Product is never stocked"), where all values in the row are >0 ("Product has never been out of stock"), and where the current date's stock is 0 yet there is at least one instance of >0 in the row ("Product has been out of stock since..."). The third part of the formula was accomplished by locating the last >0 value using =LOOKUP(2,1/(A:A>0),A:A), then offsetting by one column. I have the formulas to successfully return the results for these scenarios.
I have already tried two formulas for the fourth scenario, where the most current date's stock is >0 yet there is at least one instance of zero in the row:
=LOOKUP(2,1/(A:A=0),A:A)
=INDEX(B:B,SUMPRODUCT(MAX((Item=0)*ROW(Item))))
However, these don't work when looking for the last zero value in a row, only when looking for a non-zero value.
Some sample data:
Jan-1 Jan-2 Jan-3 Jan-4 Jan-5
1 3 5 4 3 Result: Product never out of stock
0 0 0 0 0 Result: Product never in stock
3 1 0 0 0 Result: Product out of stock since [Jan-3]
1 2 0 4 3 Result: Product in stock since [Jan-4]
It is the fourth scenario above that I cannot write a formula to complete the date input.
The only way I have been able to get this to work so far is by rearranging all of the date columns so that they flow right to left, then using INDEX and MATCH to find the first zero instance in the row in order to complete the formula. This also works successfully for finding the first >0 instance (for the third scenario for out-of-stock), so is my back-up solution. However, this is not ideal as my dates should be shown left-to-right for graphing purposes.
