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:

Improve this question
0

2 Answers 2

Reset to default
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
2
  • 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
    Commented Jun 6, 2018 at 1:23
  • 1
    Yes true... thx!
    – steve klein
    Commented Jun 6, 2018 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
2
  • 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
    Commented Jan 16, 2020 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
    Commented Jan 16, 2020 at 4:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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