Formula to display last entry in a column populated with #N/As

[T. Duprex]

I am looking for a formula that will return the last number entered into a
column. That column is part of a spreadsheet into which I enter daily
nutrition values. This particular column has my weight, which I enter
every 5 or 6 days. This weight column is initially filled in with =NA() so
that the information can be displayed on a graph without showing a value of
0 on days that I do not have an entry.

Short of duplicating another colunm,I need a formula which lets me leave
the =NA() entries while returning the last entered numerical value. My
column looks like this:


#N/A
#N/A
#N/A
198
#N/A
#N/A
#N/A
196
#N/A
#N/A
#N/A


The formula I seek would return a value of 196.

Anything I've tried comes up with #N/A, which is obviously looking at the
last #N/A. I have to find a way to have the #N/As ingnored.


TIA

 

[T. Duprex]

"T. Duprex" <tduprexATlandmark.net> wrote in

I forgot to show you the latest formula I am working with is:


=OFFSET(B8,MAX(IF(NOT(ISBLANK(B8:B190)),ROW(B8:B190),0))-ROW(B8),0)

 

[Stefi]

=LOOKUP(10^10,B:B)

10^10 is just a number that is certainly greater than the possible max value
in column B.

Regards,
Stefi

„T. Duprex†ezt írta:

 

[T. Duprex]

=LOOKUP(10^10,B:B)

Thank you Stefi;

Simple, elegant and does the trick.

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„T. Duprex†ezt írta: