Finding last cell with a value when range contains blanks

[Dave L]

I would like to do this with a formula instead of VBA if possible. I have a single column of data that is being calculated. I would like to find the last cell that has a value in it. The problem is that every other cell as you go down the column will be blank, even though there is a formula in each cell. It would look kind of like this:

1 Value
2
3 Value
4
5 Value
6
7 Value
8
9
10

So for the range of A1 to A10, I need a formula to tell me that A7 is the last one with a value. As you can see I can't just look for the first blank because that would be A2.

Any help would be greatly appreciated.

Thanks,

Dave

 

[NBVC]

Try

=LOOKUP(9.99999999999999E+307,A1:A10)

 

[Oaktree]

=LOOKUP(9.99999999999999E+307,A1:A10)

 

[barry houdini]

or if you want to find the position of the last value, rather than the value itself

=MATCH(9.99999999999999E+307,A1:A10)

assumes your "values" are numeric

 

[Dave L]

ADVERTISEMENT

Barry,

Your position formula is exactly what I needed - it works great. Thank you all for your suggestions.

Dave

 

[Dave L]

Ok, i have a new twist on this one. Is it also possible to find the position of the FIRST cell that has a value? As usual you solve one problem only to find another one.

Thanks,

Dave

 

[just_jon]

=MIN(IF(LEN(A1:A25),ROW(A1:A25)))

Where A1:A25 is the range of interest.

The above is an array formula, which requires entry via Control+Shift+Enter [CSE], not just Enter. Any direct edit of the formula will require re-entry via CSE. You can tell if the formula has been entered correctly, as it will have braces - { } - around it afterward.

 

[Dave L]

just_jon,

That did the trick. It works perfect.

Thanks for your help,

Dave