Hide #VALUE! in result of array formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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?
 
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
 
Are your values numeric, Ron?

try this

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