#VALUE! error causing calculation problems in SUM formula

invisigirl · Apr 15th, 2002, 01:59 PM

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 · Apr 15th, 2002, 02:11 PM

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 · Apr 15th, 2002, 02:41 PM

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

invisigirl · Apr 15th, 2002, 02:59 PM

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 · Apr 15th, 2002, 03:03 PM

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 · Apr 15th, 2002, 03:55 PM

Quote:

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

Apr 15th, 2002, 02:11 PM	#2
Ricky Morris Board Regular Join Date: Mar 2002 Posts: 363	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. __________________ It's never too late to learn something new. Ricky

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 15th, 2002, 01:59 PM	#1
invisigirl Board Regular Join Date: Mar 2002 Location: Oregon Posts: 130	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!

Apr 15th, 2002, 02:41 PM	#3
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	Care to post the formula that you must have in W1:W25, the range that you want to sum/total in W26?

Apr 15th, 2002, 02:59 PM	#4
invisigirl Board Regular Join Date: Mar 2002 Location: Oregon Posts: 130	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!

Apr 15th, 2002, 03:03 PM	#5
nancyo Board Regular Join Date: Mar 2002 Location: Massachusetts, USA Posts: 255	Try using the on-line help and research the ISERROR function. Can set as part of a formula: =if(iserror(A1),"0",(your formula))