Help with #VALUE! error please...
 

Hi, can anyone tell me how to change the value of an empty cell to a zero
please?

I am using =SUM(A1-B1 A2-B2 A3-B3 and so on)

It performs the sum ok and everything, but when one of the cells is blank,
it just returns a #VALUE massage, because its being asked to sum blanks etc.
How can I get around this please, and have a zero appear in the answer cells
(C1 C2 C3 etc)??

Thanks in advance,

Ted.

Help with #VALUE! error please...
 

=IF(ISNUMBER(A1),A1,0)-IF(ISNUMBER(B1),B1,0)

HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"Ted" wrote:

Hi, can anyone tell me how to change the value of an empty cell to a zero
please?

I am using =SUM(A1-B1 A2-B2 A3-B3 and so on)

It performs the sum ok and everything, but when one of the cells is blank,
it just returns a #VALUE massage, because its being asked to sum blanks etc.
How can I get around this please, and have a zero appear in the answer cells
(C1 C2 C3 etc)??

Thanks in advance,

Ted.

Help with #VALUE! error please...
 

sorr, but noup - it accounts for zeros I think, but it changes the other
correct sums to zero.

thanks anyway,
Ted.

"Gary L Brown" wrote:

=IF(ISNUMBER(A1),A1,0)-IF(ISNUMBER(B1),B1,0)

HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"Ted" wrote:

Hi, can anyone tell me how to change the value of an empty cell to a zero
please?

I am using =SUM(A1-B1 A2-B2 A3-B3 and so on)

It performs the sum ok and everything, but when one of the cells is blank,
it just returns a #VALUE massage, because its being asked to sum blanks etc.
How can I get around this please, and have a zero appear in the answer cells
(C1 C2 C3 etc)??

Thanks in advance,

Ted.

Help with #VALUE! error please...
 

On Mon, 21 Nov 2005 09:09:06 -0800, Ted wrote:

Hi, can anyone tell me how to change the value of an empty cell to a zero
please?

I am using =SUM(A1-B1 A2-B2 A3-B3 and so on)

It performs the sum ok and everything, but when one of the cells is blank,
it just returns a #VALUE massage, because its being asked to sum blanks etc.
How can I get around this please, and have a zero appear in the answer cells
(C1 C2 C3 etc)??

Thanks in advance,

Ted.

=SUM(A1-B1) should not produce an error if either or both cells are blank.

And neither will the equivalent and simpler formula =A1-B1

I suspect that either A1 or B1 or both are NOT blank. What is the result of
these formulas:
=ISBLANK(A1)
=ISBLANK(B1)

One method of outputting a zero unless A1 and B1 BOTH contain numbers is:

=IF(COUNT(A1:B1)=2,A1-B1,0)


--ron

Help with #VALUE! error please...
 

hi, no - they contain formula, sorry. Is there a way of gettign it to ignore
the formula and return a zero when there are no numbers in the cell - it
displays the answer to a previous formula.

thanks, Ted.

"Ron Rosenfeld" wrote:

On Mon, 21 Nov 2005 09:09:06 -0800, Ted wrote:

Hi, can anyone tell me how to change the value of an empty cell to a zero
please?

I am using =SUM(A1-B1 A2-B2 A3-B3 and so on)

It performs the sum ok and everything, but when one of the cells is blank,
it just returns a #VALUE massage, because its being asked to sum blanks etc.
How can I get around this please, and have a zero appear in the answer cells
(C1 C2 C3 etc)??

Thanks in advance,

Ted.

=SUM(A1-B1) should not produce an error if either or both cells are blank.

And neither will the equivalent and simpler formula =A1-B1

I suspect that either A1 or B1 or both are NOT blank. What is the result of
these formulas:
=ISBLANK(A1)
=ISBLANK(B1)

One method of outputting a zero unless A1 and B1 BOTH contain numbers is:

=IF(COUNT(A1:B1)=2,A1-B1,0)


--ron

Help with #VALUE! error please...
 

On Mon, 21 Nov 2005 12:09:06 -0800, Ted wrote:

hi, no - they contain formula, sorry.

Ah -- that makes a big difference. What is the formula?

Is there a way of gettign it to ignore
the formula and return a zero when there are no numbers in the cell - it
displays the answer to a previous formula.

Post the formulas from A1 & B1.

What was the problem with the formula I suggested?

One method of outputting a zero unless A1 and B1 BOTH contain numbers is:

=IF(COUNT(A1:B1)=2,A1-B1,0)



--ron

Help with #VALUE! error please...
 

Hi Ron, its:

=IF(AND(C4-D40,COUNT(C4:D4)=2),IF(ROUND(7/(C4-D4),5)0.5,ROUND(7/(C4-D4),5),""),"")

quite a handfull I'm afraid. the actual cell reference for where the formula
result is to display is E43

the currnet formula is:

=SUM(C23-Sheet1!C23)

the first cell is on 'Sheet 2' and the second on 'Sheet 1'

Any ideas please??

Ted.



"Ron Rosenfeld" wrote:

On Mon, 21 Nov 2005 12:09:06 -0800, Ted wrote:

hi, no - they contain formula, sorry.

Ah -- that makes a big difference. What is the formula?

Is there a way of gettign it to ignore
the formula and return a zero when there are no numbers in the cell - it
displays the answer to a previous formula.

Post the formulas from A1 & B1.

What was the problem with the formula I suggested?

One method of outputting a zero unless A1 and B1 BOTH contain numbers is:

=IF(COUNT(A1:B1)=2,A1-B1,0)



--ron

Help with #VALUE! error please...
 

On Mon, 21 Nov 2005 13:02:01 -0800, Ted wrote:

Hi Ron, its:

=IF(AND(C4-D40,COUNT(C4:D4)=2),IF(ROUND(7/(C4-D4),5)0.5,ROUND(7/(C4-D4),5),""),"")

So this formula is in Sheet1!C23 and also in Sheet2!C23



quite a handfull I'm afraid. the actual cell reference for where the formula
result is to display is E43

the currnet formula is:

=SUM(C23-Sheet1!C23)

in Sheet2!E43 /


the first cell is on 'Sheet 2' and the second on 'Sheet 1'

Any ideas please??



1. Change the formulas in Sheet1!C23 and Sheet2!C23 to:

=IF(AND(C4<D4,COUNT(C4:D4)=2),IF(ROUND(7/(C4-D4),5)0.5,ROUND(7/(C4-D4),5),""),"")

2. Change your SUM formula in E43 to:

=IF(COUNT(Sheet2:Sheet1!C23)=2,SUM(Sheet2:Sheet1!C 23),0)


--ron

Help with #VALUE! error please...
 

Hi Ron, thanks - that works great $;-)

Ted.


"Ron Rosenfeld" wrote:

On Mon, 21 Nov 2005 13:02:01 -0800, Ted wrote:

Hi Ron, its:

=IF(AND(C4-D40,COUNT(C4:D4)=2),IF(ROUND(7/(C4-D4),5)0.5,ROUND(7/(C4-D4),5),""),"")

So this formula is in Sheet1!C23 and also in Sheet2!C23



quite a handfull I'm afraid. the actual cell reference for where the formula
result is to display is E43

the currnet formula is:

=SUM(C23-Sheet1!C23)

in Sheet2!E43 /


the first cell is on 'Sheet 2' and the second on 'Sheet 1'

Any ideas please??



1. Change the formulas in Sheet1!C23 and Sheet2!C23 to:

=IF(AND(C4<D4,COUNT(C4:D4)=2),IF(ROUND(7/(C4-D4),5)0.5,ROUND(7/(C4-D4),5),""),"")

2. Change your SUM formula in E43 to:

=IF(COUNT(Sheet2:Sheet1!C23)=2,SUM(Sheet2:Sheet1!C 23),0)


--ron

Help with #VALUE! error please...
 

On Mon, 21 Nov 2005 14:29:02 -0800, Ted wrote:

Hi Ron, thanks - that works great $;-)

Ted.

Ahh -- You're most welcome. Good thing, too, as I'm out of town starting
tomorrow for the rest of the week!


--ron