Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|