Lookup most recent data point ignoring zeros and blank cells

[Krucial155]

I currently have a metric spreadsheet that is updated daily. After I put in the data, I have a function to calculate the sum and percentages of certain metrics.

I currently have a lookup function that I use for some input fields to ignore the blank cells and only return the last cell with numeric value (which will be the most recent day's data). However, when I try to use this for different metrics to pull onto another sheet, it is taking the 0s from this row. How can I have a lookup function that ignores blank cells, and ignores zeros, but still pulls the most data.

=LOOKUP(2,1/(3:3<>""),3:3)

That is the function I found for my input field. It allows us to pull the most recent cell (total followers) excluding cells in that row that are blank.

I'm very new to Excel and self-taught myself over the course of the past 2 weeks. I would love to get some guidance on how to accomplish my goal of eliminating Zeros from the lookup as well, but also understanding the function part by part, should somebody have the time.

Thank you.

 

[alz]

How about this?
=IF(3:3>0,LOOKUP(2,1/(3:3<>""),3:3))

 

[Krucial155]

How about this?
=IF(3:3>0,LOOKUP(2,1/(3:3<>""),3:3))

Thank you for your reply, I'm getting a #spill error

 

[jasonb75]

=LOOKUP(2,1/(3:3<>""),3:3)

As with many things, it is much easier for us to give you more accurate guidance with a visual example that will help to avoid misinterpretation, from observations on your post though.

You don't need the (3:3<>"") part with numeric data,

=LOOKUP(1e+100,3:3)

will work fine and is far more efficient. Likewise, with text data you could use

=LOOKUP(REPT("z",255),3:3)

as long as the cells in 3:3 are empty, if the last cell contains a formula that uses "" to show a blank then that will still be picked up by the text version, the numeric one will ignore it.

To ignore zero values, you could use something like

=LOOKUP(1e+100,1/(1/(3:3))

which will turn any zeros or empty cells into #DIV/0! errors that the lookup will ignore, although I would advise using something like A3:Z3 instead of 3:3. Use of entire rows or columns when not needed makes formulas very inefficient and can make your formulas very slow of you have a lot of them.

 

[Krucial155]

This is the row that that function is currently working on, where I can use the function to take just the most recent cell, where it'll put it in my daily report field for Total Followers. Row 3

This is a row where I want to pull daily data and put it on another sheet for a daily, printable report. I want to take the last, non-zero field on the Daily Messages portion. Daily messages is row 27

 

[Krucial155]

@jasonb75 That seems to have worked! And I really appreciate the breakdown!

 

[jasonb75]

You're welcome

Just one additional point from seeing the screen capture, the formula is doing what you asked for and looking for the last non 0 value in the row. It will not check to see it the dates in the 2 sheets match.

If you wanted to cross reference dates as well then that would need something like FILTER, INDEX or HLOOKUP depending on exactly how you want the results organised.

For information, if you check my signature block below this post, you will see a link to XL2BB which is a tool designed for the forum that lets you post small sections of your sheet in a format that you can copy and paste to excel with any existing formulas included. This is quite useful when what you need is not quite so easy to explain.

 

[Krucial155]

You're welcome

Just one additional point from seeing the screen capture, the formula is doing what you asked for and looking for the last non 0 value in the row. It will not check to see it the dates in the 2 sheets match.

If you wanted to cross reference dates as well then that would need something like FILTER, INDEX or HLOOKUP depending on exactly how you want the results organised.

For information, if you check my signature block below this post, you will see a link to XL2BB which is a tool designed for the forum that lets you post small sections of your sheet in a format that you can copy and paste to excel with any existing formulas included. This is quite useful when what you need is not quite so easy to explain.

Oh I am actually going to use this function to make a small little table, which the 2nd sheet will reference. The 2nd sheet is suppose to change each day (it's archived by a different system)

While I have you, is there an easy to convert this function to do the same thing with percentages? Making it look up the last in a row that's not blank or -100%?

Lookup the last weekly change percentage that isn't -100 (from the input 7 days ago to blank aka 0) or #div/0 result from trying 0/0.

 

[jasonb75]

The numeric method will work with anything that excel sees as a number, which will include percentages, dates, times, currency amounts etc as long as they are in a valid format.

As you have 2 criteria this time though, it would be a little different. I've started off with a formula that looks at the values in A3:Z3, which you can change as needed.

=LOOKUP(2,(1/(1/($A3:$Z3))<>-100%,$A3:$Z3)

I think that I have the formula correct but I haven't tested it in excel.

 

[Krucial155]

The numeric method will work with anything that excel sees as a number, which will include percentages, dates, times, currency amounts etc as long as they are in a valid format.

As you have 2 criteria this time though, it would be a little different. I've started off with a formula that looks at the values in A3:Z3, which you can change as needed.

=LOOKUP(2,(1/(1/($A3:$Z3))<>-100%,$A3:$Z3)

I think that I have the formula correct but I haven't tested it in excel.

Hmmm. Yeah I get this error.