Google-sheets – Formula that displays the last non-zero value cell

formulasgoogle sheets

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:

Best Answer

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

Related Solutions

Google-sheets – Scan row for value, get column of that match, apply column in another formula in a different cell

The match formula will tell you which column within 1:1 matches today(); in conjunction with index we can use that column number to get the address of the cell holding the Yes/No data for today. This becomes useful when paired through string concatenation (&) with the address of the first cell of the Yes/No data ($A$2 here) -- the 'dynamic' range you wanted can now be interpreted using the indirect formula!

All together, you're looking at the following formula for B3 in your example:

=COUNTIF(INDIRECT("$A$2:"&CELL("address",INDEX($1:$2,2,MATCH(TODAY(),$1:$1)))),"Yes")

edit

I temporarily forgot about filter, which makes this task much easier: =COUNTIF(FILTER(2:2,1:1<=TODAY()),"No")

Google-sheets – Universal formula for generating Google Spreadsheet hyperlinks from cell contents

In Google Sheets, when you copy a formula that uses relative references to another cell, that references are automatically updated.

A basic example

If in cell B2 the formula is =A2 and you copy it and paste to cell B43, the resulting formula will be =A43.

An example for the specific case of this question

If in cell B2 the formula is
=HYPERLINK(CONCATENATE("http://example.com?id=",A2),A2)
and you copy it and paste to cell B455, the resulting formula will be
=HYPERLINK(CONCATENATE("http://example.com?id=",A455),A455).

References
Add formulas to a spreadsheet - Docs editors help