Hide #VALUE! in result of array formula
 

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

Hide #VALUE! in result of array formula
 

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

"Ron Weaver" wrote in message
...
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

Hide #VALUE! in result of array formula
 

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

"T. Valko" wrote:

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

"Ron Weaver" wrote in message
...
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

Hide #VALUE! in result of array formula
 

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" wrote:

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

"Ron Weaver" wrote in message
...
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

Hide #VALUE! in result of array formula
 

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

"Ron Weaver" wrote in message
...
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" wrote:

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

"Ron Weaver" wrote in message
...
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

Hide #VALUE! in result of array formula
 

Are your values numeric, Ron?

try this

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

"Ron Weaver" wrote:

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" wrote:

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

"Ron Weaver" wrote in message
...
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

Hide #VALUE! in result of array formula
 

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

"daddylonglegs" wrote:

Are your values numeric, Ron?

try this

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

"Ron Weaver" wrote:

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" wrote:

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

"Ron Weaver" wrote in message
...
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