| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT clue needed | Excel Discussion (Misc queries) | |||
| Formula result as real empty/blank cell | Excel Worksheet Functions | |||
| Rs.10,250.00 formula Rupees ten thousand two hundred fifty only | New Users to Excel | |||
| VBA | Excel Worksheet Functions | |||
| Array formula weird result | Excel Discussion (Misc queries) | |||
Linear Mode
