1

I have a report that is able to pull/display values based on yesterday's values.

Is there a way I can modify the formula to ignore cells that contain zeros "0s" and to continue to reference/pull the LAST cell that contains a numeric value?

Current formula:

=INDEX(SM!40:40,COUNTA(SM!A40:AG40))

Screenshot:

3

You can find the last numeric value in row 1 (excluding 0s, blanks and text) with:

=index(filter(1:1,1:1<>0),count(filter(1:1,1:1<>0)))

INDEX
FILTER
COUNT

| improve this answer | |
  • This is great! Is it possible return the column reference of the last numeric (non zero) number in a row? I can do it in Excel, but the formula is not working in Sheets. – steve klein Jun 6 '18 at 1:23
  • 1
    Yes true... thx! – steve klein Jun 6 '18 at 1:25
1

In my test, changing the row reference to a column reference (column U in this case), the above formula returns the NEXT-to-last non-zero value. I was able to get the LAST value as follows, but I don't know whether this formula would work generally or there is something particular about my spreadsheet that requires the "+1," so I hesitate to recommend this as a general solution:

=index(filter(U:U,U:U<>0),count(filter(U:U,U:U<>0))+1)
| improve this answer | |
  • Actually, I must amend my previous comment. Now that more values have been added to my column U, the formula returns a totally wrong result. I should mention that the non-zero values in my column are mostly negative numbers. Maybe that confounds the formula. – Steve Jan 16 at 4:24
  • On another site, I found a formula that seems to work correctly: =ArrayFormula(iferror(lookup(1,U:U/U:U,U:U))) where my figures are created by a formula in each cell of Column U; the formula produces a result of 0 where the data isn't supplied yet. (This is a spreadsheet updated daily with the previous day's results.) Source: infoinspired.com/google-docs/spreadsheet/… – Steve Jan 16 at 4:37

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.