Last Value in a Column when Value <> 0

  • Thread starter SanCarlosCyclist
  • Start date
S

SanCarlosCyclist

Hi, can someone provide me a formula that populates a cell with the
last value in a column that does not = 0?
Below is an example
3
6
7
0
0
0

The goal is to populate a cell with the value of 7. I am currently
using the below formula that populates the last value of a column:
=INDEX('Retirement Total'!B:B,MATCH(9.99999999999999E+307,'Retirement
Total'!B:B))

I do not however know how to change this to not populate the last
value when it is zero. Your help is appreciated.
 
T

T. Valko

Assuming you want the last *numeric* value that <>0.

Try something like this:

=LOOKUP(1E100,1/'Retirement Total'!B1:B20,'Retirement Total'!B1:B20)

Note: unless you're using Excel 2007 (or greater) you can't reference the
entire column B:B. You'd have to specify a range.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top