| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
blank to zero conversion?
I wish to refer to a column of numbers in a formula where a zero is represented by a blank rather than the number zero in the reference column. Is there a function which will convert the blank to a zero to avoid an error? (e.g.) the following results in an error in cell B1 so I guess I need a function which I can use in the formula in B1 which will convert the blank in A1 to a numeric zero? CELL A1 CONTAINS blank CELL A2 CONTAINS 2 CELL B1 CONTAINS =A1+A2 -- Joe Miller ------------------------------------------------------------------------ Joe Miller's Profile: http://www.excelforum.com/member.php...o&userid=29900 View this thread: http://www.excelforum.com/showthread...hreadid=570145 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
blank to zero conversion?
Hi!
Instead of this: = A1+A2 Use this: =SUM(A1,A2) The SUM() function will ignore TEXT entries. Biff "Joe Miller" wrote in message ... I wish to refer to a column of numbers in a formula where a zero is represented by a blank rather than the number zero in the reference column. Is there a function which will convert the blank to a zero to avoid an error? (e.g.) the following results in an error in cell B1 so I guess I need a function which I can use in the formula in B1 which will convert the blank in A1 to a numeric zero? CELL A1 CONTAINS blank CELL A2 CONTAINS 2 CELL B1 CONTAINS =A1+A2 -- Joe Miller ------------------------------------------------------------------------ Joe Miller's Profile: http://www.excelforum.com/member.php...o&userid=29900 View this thread: http://www.excelforum.com/showthread...hreadid=570145 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
blank to zero conversion?
THANKS BIFF JOE MILLER Biff Wrote: Hi! Instead of this: = A1+A2 Use this: =SUM(A1,A2) The SUM() function will ignore TEXT entries. Biff "Joe Miller" wrote in message ... I wish to refer to a column of numbers in a formula where a zero is represented by a blank rather than the number zero in the reference column. Is there a function which will convert the blank to a zero to avoid an error? (e.g.) the following results in an error in cell B1 so I guess I need a function which I can use in the formula in B1 which will convert the blank in A1 to a numeric zero? CELL A1 CONTAINS blank CELL A2 CONTAINS 2 CELL B1 CONTAINS =A1+A2 -- Joe Miller ------------------------------------------------------------------------ Joe Miller's Profile: http://www.excelforum.com/member.php...o&userid=29900 View this thread: http://www.excelforum.com/showthread...hreadid=570145 -- Joe Miller ------------------------------------------------------------------------ Joe Miller's Profile: http://www.excelforum.com/member.php...o&userid=29900 View this thread: http://www.excelforum.com/showthread...hreadid=570145 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
blank to zero conversion?
You're welcome!
Biff "Joe Miller" wrote in message ... THANKS BIFF JOE MILLER Biff Wrote: Hi! Instead of this: = A1+A2 Use this: =SUM(A1,A2) The SUM() function will ignore TEXT entries. Biff "Joe Miller" wrote in message ... I wish to refer to a column of numbers in a formula where a zero is represented by a blank rather than the number zero in the reference column. Is there a function which will convert the blank to a zero to avoid an error? (e.g.) the following results in an error in cell B1 so I guess I need a function which I can use in the formula in B1 which will convert the blank in A1 to a numeric zero? CELL A1 CONTAINS blank CELL A2 CONTAINS 2 CELL B1 CONTAINS =A1+A2 -- Joe Miller ------------------------------------------------------------------------ Joe Miller's Profile: http://www.excelforum.com/member.php...o&userid=29900 View this thread: http://www.excelforum.com/showthread...hreadid=570145 -- Joe Miller ------------------------------------------------------------------------ Joe Miller's Profile: http://www.excelforum.com/member.php...o&userid=29900 View this thread: http://www.excelforum.com/showthread...hreadid=570145 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
blank to zero conversion?
If I understand what you described, I would put an IF function in B1 to check
the value of A1 in order to avoid an error. Also, if I understand A1 to have a blank and and A2 = 2 (in this example), then B1 should be the value of 0+2. I am not sure of the logic behind knowing what numbers you wish to add in column A, so for this example I am going to just address the cells A1 & A2 in checking for a blank. FORMULA for B1 =IF(OR(A1="",A1=" "), A2, IF(OR(A2="",A2=" "), A1, A1+A2)) I tested this and it seems to work fine. However, I did get a value of 0 (zero) in B1 with both A1 & A2 were empty. Not sure why, but maybe this will work for you. Les "Joe Miller" wrote: I wish to refer to a column of numbers in a formula where a zero is represented by a blank rather than the number zero in the reference column. Is there a function which will convert the blank to a zero to avoid an error? (e.g.) the following results in an error in cell B1 so I guess I need a function which I can use in the formula in B1 which will convert the blank in A1 to a numeric zero? CELL A1 CONTAINS blank CELL A2 CONTAINS 2 CELL B1 CONTAINS =A1+A2 -- Joe Miller ------------------------------------------------------------------------ Joe Miller's Profile: http://www.excelforum.com/member.php...o&userid=29900 View this thread: http://www.excelforum.com/showthread...hreadid=570145 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
blank to zero conversion?
THANKS LES JOE MILLER WLMPilot Wrote: If I understand what you described, I would put an IF function in B1 to check the value of A1 in order to avoid an error. Also, if I understand A1 to have a blank and and A2 = 2 (in this example), then B1 should be the value of 0+2. I am not sure of the logic behind knowing what numbers you wish to add in column A, so for this example I am going to just address the cells A1 & A2 in checking for a blank. FORMULA for B1 =IF(OR(A1="",A1=" "), A2, IF(OR(A2="",A2=" "), A1, A1+A2)) I tested this and it seems to work fine. However, I did get a value of 0 (zero) in B1 with both A1 & A2 were empty. Not sure why, but maybe this will work for you. Les "Joe Miller" wrote: I wish to refer to a column of numbers in a formula where a zero is represented by a blank rather than the number zero in the reference column. Is there a function which will convert the blank to a zero to avoid an error? (e.g.) the following results in an error in cell B1 so I guess I need a function which I can use in the formula in B1 which will convert the blank in A1 to a numeric zero? CELL A1 CONTAINS blank CELL A2 CONTAINS 2 CELL B1 CONTAINS =A1+A2 -- Joe Miller ------------------------------------------------------------------------ Joe Miller's Profile: http://www.excelforum.com/member.php...o&userid=29900 View this thread: http://www.excelforum.com/showthread...hreadid=570145 -- Joe Miller ------------------------------------------------------------------------ Joe Miller's Profile: http://www.excelforum.com/member.php...o&userid=29900 View this thread: http://www.excelforum.com/showthread...hreadid=570145 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Imported Data creates blank cells that aren't really blank | Excel Worksheet Functions | |||
| enter numbers in blank cells | Excel Worksheet Functions | |||
| Counting blank and filled cells within a range. | Excel Discussion (Misc queries) | |||
| blank cells | Excel Discussion (Misc queries) | |||
| Blank cell conversion to text | New Users to Excel | |||
Hybrid Mode
