#VALUE! Error Message from Formula Referencing a Blank Cell
Posted by Ted Dankovich on December 13, 2001 12:17 PM
I'm using Excel 2000 to create a monthly financial consolidation worksheet. The source data is created from an Excel worksheet that imports data from a Unix software application.
If there is no source data in a cell from the extract, then the cell is "blank" (similar to using the spacebar to populate the cell). I use the ISERROR function to satisfy formula the first referenced cell that calls the extract data (with 0 as the first criteria if the original cell is blank) Using 0 does not create a number format or populate the field, it remains blank. Otherwise I get the value from the extract data source,which populates the data into my analysis correctly....
Now I want to Sum the data to create totals. When I sum the data fields, if a "blank" field exists I get the #VALUE! error message. I've tested the formats to find that I need to remove the format from the source data (written by a 3rd party developer).
I need to identify a formula function that will either ignore the "blank" format and sum the remaining cells or a macro to reformat the "blank" imported cells without disturbing the other data.
I am automating this to be a monthly process, so a manual re-format will not be an option.
Please e-mail any suggestions that will resolve this issue. Thanks!
Posted by Gary on December 13, 2001 12:25 PM
You could use an array formula along the lines of =sum(if(Sum_range="",0,Sum_range)).
Gary
Posted by Ted on December 13, 2001 12:34 PM
Re: #VALUE! Error Message - Array Formula
Great Idea Gary, but didn't work. The data I want to sum is not in continuous rows. I tried to sum the individual cells in your formula, still got the #VALUE! result. Added IF(ISERROR... function and only got a 0 as a result. Need sum of data.
Thanks...
Posted by Aladin Akyurek on December 13, 2001 12:45 PM
Ted --
Normally, a cell containing an ordinary space or an empty cell shouldn't cause a #VALUE! error, becasue SUM will ignore such a cell.
Care to post the formula that you use?
Aladin
Posted by Aladin Akyurek on December 13, 2001 1:13 PM
Re: #VALUE! Error Message - Array Formula
Tedd --
I don't understand what you mean by "not in continuous rows". However, would you check whether the following will work?
=SUMPRODUCT((ISNUMBER(A1:A100)+0),(A1:A100))
Adapt the range to your situation.
And, would you evaluate the following formula next to a cell that gives the trouble?
=CODE(the-cell-that-gives-trouble)
Aladin
===========
Posted by Ted on December 13, 2001 1:20 PM
Re: #VALUE! Error Message - Formula Used
Here's the formula:
IF(ISERROR(+e11+e16+e29)="",0,+e11+e16+e29)where e29 formula is IF(ISERROR(+WK1!d45),0,+WK1!d45).
In this example, e29 returns a blank cell resulting from WK1!d45 being a blank cell.
If I clear the format in WK1!d45 or place a number in WK1!d45, then the formulas return the correct answer.
Ted--
Posted by Aladin Akyurek on December 13, 2001 1:44 PM
Re: #VALUE! Error Message - Formula Used
> IF(ISERROR(+e11+e16+e29)="",0,+e11+e16+e29)where e29 formula is IF(ISERROR(+WK1!d45),0,+WK1!d45).
I think the following formula should simply work.
=SUM(E11,E16,E19)
where e19 is simply =WK1!d45 if this WK1 cell is numeric or otherwise contains a space as you stated earlier.
You can check what is in WK1!d45 when it's non-numeric by using
=CODE(WK1!D45)
Aladin
===========
