Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I count blank cells as a zero, when using formula please??
how can I get sums to perform as normal, even when it is confronted by
empty cells? I realise that if I am computing, say, A1-B1 and A2-B2 and there is no data in one of them (e.g. A2-B2) then I will get a #VALUE! error message because of no data etc. How can I get around this please, and have the answer cell display a zero for the result, when there is no data to compute E.G. {using the fake data of 5-6=1 and 0-3=#VALUE!} from: A1[5] - B1[6] = C1[1] A2[empty cell] - B2[3] = #VALUE! to: A1[5] - B1[6] = C1[1] A2[empty cell] - B2[3] = 0 I cant have zeros appear in the dependant cells because it alters other formula, so just neet it to class a blank/empty cell as a zero. Any ideas please?? Ted. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I count blank cells as a zero, when using formula please??
=IF(OR(A2="",B2=""),0,A2-B2)
-- Ian -- "Ted" wrote in message ... how can I get sums to perform as normal, even when it is confronted by empty cells? I realise that if I am computing, say, A1-B1 and A2-B2 and there is no data in one of them (e.g. A2-B2) then I will get a #VALUE! error message because of no data etc. How can I get around this please, and have the answer cell display a zero for the result, when there is no data to compute E.G. {using the fake data of 5-6=1 and 0-3=#VALUE!} from: A1[5] - B1[6] = C1[1] A2[empty cell] - B2[3] = #VALUE! to: A1[5] - B1[6] = C1[1] A2[empty cell] - B2[3] = 0 I cant have zeros appear in the dependant cells because it alters other formula, so just neet it to class a blank/empty cell as a zero. Any ideas please?? Ted. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I count blank cells as a zero, when using formula plea
Hi, thanks for your help, but unfortunately it just makes it return zeros for
all of the sums - any ideas?? thanks again, Ted "Ian" wrote: =IF(OR(A2="",B2=""),0,A2-B2) -- Ian -- "Ted" wrote in message ... how can I get sums to perform as normal, even when it is confronted by empty cells? I realise that if I am computing, say, A1-B1 and A2-B2 and there is no data in one of them (e.g. A2-B2) then I will get a #VALUE! error message because of no data etc. How can I get around this please, and have the answer cell display a zero for the result, when there is no data to compute E.G. {using the fake data of 5-6=1 and 0-3=#VALUE!} from: A1[5] - B1[6] = C1[1] A2[empty cell] - B2[3] = #VALUE! to: A1[5] - B1[6] = C1[1] A2[empty cell] - B2[3] = 0 I cant have zeros appear in the dependant cells because it alters other formula, so just neet it to class a blank/empty cell as a zero. Any ideas please?? Ted. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I count blank cells as a zero, when using formula please??
Hi Ted
I don't quite understand the problem. With the values shown and the formula of A1-B1, and A2-B2 you should get -1 and -3 respectively. If you are getting a #VALUE for A2-B2, then A2 is probably not blank, but contains a space or a "" value as a result of another formula. You could use =IF(COUNT(A2:B2)1,A2-B2,0) or =IF(AND(ISNUMBER(A2),ISNUMBER(B2)),A2-B2,0) Regards Roger Govier Ted wrote: how can I get sums to perform as normal, even when it is confronted by empty cells? I realise that if I am computing, say, A1-B1 and A2-B2 and there is no data in one of them (e.g. A2-B2) then I will get a #VALUE! error message because of no data etc. How can I get around this please, and have the answer cell display a zero for the result, when there is no data to compute E.G. {using the fake data of 5-6=1 and 0-3=#VALUE!} from: A1[5] - B1[6] = C1[1] A2[empty cell] - B2[3] = #VALUE! to: A1[5] - B1[6] = C1[1] A2[empty cell] - B2[3] = 0 I cant have zeros appear in the dependant cells because it alters other formula, so just neet it to class a blank/empty cell as a zero. Any ideas please?? Ted. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I count blank cells as a zero, when using formula plea
thanks Roger - I'll give it a try,
Ted. "Roger Govier" wrote: Hi Ted I don't quite understand the problem. With the values shown and the formula of A1-B1, and A2-B2 you should get -1 and -3 respectively. If you are getting a #VALUE for A2-B2, then A2 is probably not blank, but contains a space or a "" value as a result of another formula. You could use =IF(COUNT(A2:B2)1,A2-B2,0) or =IF(AND(ISNUMBER(A2),ISNUMBER(B2)),A2-B2,0) Regards Roger Govier Ted wrote: how can I get sums to perform as normal, even when it is confronted by empty cells? I realise that if I am computing, say, A1-B1 and A2-B2 and there is no data in one of them (e.g. A2-B2) then I will get a #VALUE! error message because of no data etc. How can I get around this please, and have the answer cell display a zero for the result, when there is no data to compute E.G. {using the fake data of 5-6=1 and 0-3=#VALUE!} from: A1[5] - B1[6] = C1[1] A2[empty cell] - B2[3] = #VALUE! to: A1[5] - B1[6] = C1[1] A2[empty cell] - B2[3] = 0 I cant have zeros appear in the dependant cells because it alters other formula, so just neet it to class a blank/empty cell as a zero. Any ideas please?? Ted. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I count blank cells as a zero, when using formula plea
I'm not sure what you need. The formula I posted is (in words). IF A2 is
blank OR B2 is blank, THEN this cell=0, ELSE this cell=A2-B2. If this is not what you need, can you be clearer about your requirements, please? -- Ian -- "Ted" wrote in message ... Hi, thanks for your help, but unfortunately it just makes it return zeros for all of the sums - any ideas?? thanks again, Ted "Ian" wrote: =IF(OR(A2="",B2=""),0,A2-B2) -- Ian -- "Ted" wrote in message ... how can I get sums to perform as normal, even when it is confronted by empty cells? I realise that if I am computing, say, A1-B1 and A2-B2 and there is no data in one of them (e.g. A2-B2) then I will get a #VALUE! error message because of no data etc. How can I get around this please, and have the answer cell display a zero for the result, when there is no data to compute E.G. {using the fake data of 5-6=1 and 0-3=#VALUE!} from: A1[5] - B1[6] = C1[1] A2[empty cell] - B2[3] = #VALUE! to: A1[5] - B1[6] = C1[1] A2[empty cell] - B2[3] = 0 I cant have zeros appear in the dependant cells because it alters other formula, so just neet it to class a blank/empty cell as a zero. Any ideas please?? Ted. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need formula that will automatically count the filled cells. | Excel Discussion (Misc queries) | |||
Conditional formatting...cont. from 9/25 | Excel Discussion (Misc queries) | |||
formula to count cells not blanK | Excel Worksheet Functions | |||
blank cells | Excel Discussion (Misc queries) | |||
Formula to count the cells in a range that have a fill color. | Excel Discussion (Misc queries) |