Hide #VALUE! in result of array formula

[Guest]

I am using the following array formula in an Excel spreadsheet:
{=INDIRECT(ADDRESS(MAX((ROW(30:34)*(A30:A34<>""))),COLUMN(A:A)))}.
The idea is to show the last non blank amount in a report. The problem is,
if all cells are left blank, my report shows #VALUE!, which I would like to
hide. I have tried some IF( statements, but if any cells are left blank it
shows a blank on the report even if one value is there. I looked at
conditional formatting to turn the fonts white when #VALUE! or ISERROR
conditions are there. Didn't work. There is probably an easy fix, but I can't
seem to figure it out.
Thanks

 

[T. Valko]

This will leave the cell blank if no entries are found (array entered):

=IF(LEN(A30:A34)>0,LOOKUP(2,1/(A30:A34<>""),A30:A34),"")

Biff

 

[Guest]

Thanks Biff,
I never would have figured that one out. It works great!
Thanks
Ron

 

[Guest]

Hi Biff,
Problem: If the first cell (A30) is left blank, the report cell will be
blank even though cells A31 through A34 have values in them. Any ideas?

 

[T. Valko]

What type of data is in this range, TEXT or NUMBERS, or both?

Are these values the result of a formula? If so, do these formulas return
formula blanks under certain conditions?

Since you didn't mention any of this in your original post I used a
"generic" formula to try and cover all the bases.

Biff

 

[Guest]

Are your values numeric, Ron?

try this

=IF(COUNT(A30:A34),LOOKUP(9.99999999999999E+307,A30:A34),"")

 

[Guest]

I believe that did it. The values are numeric.
Thanks to you and Biff.
Ron