#VALUE! error causing calculation problems in SUM formula

[invisigirl]

Okay. I've heard people say that you should not get a #VALUE! error simply because of blank cells - that a blank cell is interpreted as zero. Well, that does not seem to be the case for me. I have a spreadsheet that is summing data in column W for all the previous cells (Ex: A3:V3) in that row. Row 1 will be fine, row 2 will be fine, but row 3 doesn't have any data, so cell W3 displays the error message "#VALUE!"

Now, in cell W26, I'm summing W1:W25. I have 3 instances in column W where the formula result displays as #VALUE!. Therefore, W26 is also displaying as #VALUE!.

So, in very literal terms (because I'm much more of an English person than a computer guru), could someone explain to me how to fix this?

Thanks in advance to all the smarties!

 

[Ricky Morris]

Make sure that there are no hidden columns with errors that you're including in you sum formula.

Try deleting the sum formulas and use Excel to find the errors by: Choosing Edit > Goto from the menu, click Special, click Formulas and uncheck all but errors, click OK. Excel should select all cells with errors, or alert you if none is found.

 

[Aladin Akyurek]

Care to post the formula that you must have in W1:W25, the range that you want to sum/total in W26?

 

[invisigirl]

Okay, here's what happened. I looked at every cell being pulled in for the formula, and there was no data in any of them. HOWEVER, I thought, just for the heck of it, I'd try clearing those cells, and that worked. What the heck? I sincerely do not understand the workings of the world sometimes.

But, just out of curiosity, IS there a way to format a column so that if a formula results in an error message, a zero is returned instead? I know maybe it's better to find the error, but in cases like the question I asked, where it was something ridiculous like it was, and it would involve me clearing the offending cells (and the spreadsheet might be thousands of rows long), how would one go about trading errors for zeroes? If there is, in fact, a way.

Thanks for your help so far, people!

 

[nancyo]

Try using the on-line help and research the ISERROR function. Can set as part of a formula:

=if(iserror(A1),"0",(your formula))

 

[Aladin Akyurek]

On 2002-04-15 14:03, nancyo wrote:
Try using the on-line help and research the ISERROR function. Can set as part of a formula:

=if(iserror(A1),"0",(your formula))

No need for double quotes around the zero.

Aladin