Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula miscalculating blank cells
I am trying to create a conditional formula that will tag cells with data
points =0 as "1", if not =0, then a tag of "0". Excel is identifying blank cells as though they contained a zero and therefore tags these blank cells as a "1" when I need them to be reflected as a "0". Can anyone help / advise how to correct this. Thanks + happy new year |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula miscalculating blank cells
One way, which would also trap* any wrong results caused by text strings but
will treat text numbers as numbers *it'll return a blank: "" for text With source data running in A1 down In B1, copied down: =IF(A1="",0,IF(AND(ISNUMBER(A1+0),A1=0),1,"")) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Rob in Sydney" wrote: I am trying to create a conditional formula that will tag cells with data points =0 as "1", if not =0, then a tag of "0". Excel is identifying blank cells as though they contained a zero and therefore tags these blank cells as a "1" when I need them to be reflected as a "0". Can anyone help / advise how to correct this. Thanks + happy new year |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula miscalculating blank cells
Max
Thanks ... your answer did the trick. Cheers "Max" wrote: One way, which would also trap* any wrong results caused by text strings but will treat text numbers as numbers *it'll return a blank: "" for text With source data running in A1 down In B1, copied down: =IF(A1="",0,IF(AND(ISNUMBER(A1+0),A1=0),1,"")) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Rob in Sydney" wrote: I am trying to create a conditional formula that will tag cells with data points =0 as "1", if not =0, then a tag of "0". Excel is identifying blank cells as though they contained a zero and therefore tags these blank cells as a "1" when I need them to be reflected as a "0". Can anyone help / advise how to correct this. Thanks + happy new year |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula miscalculating blank cells
Welcome, Rob. Glad it helped
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Rob in Sydney" wrote in message ... Max Thanks ... your answer did the trick. Cheers |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula miscalculating blank cells
=IF(OR(A1<0,A1=""),0,1)
Gord Dibben MS Excel MVP On Sun, 30 Dec 2007 16:57:00 -0800, Rob in Sydney <Rob in wrote: I am trying to create a conditional formula that will tag cells with data points =0 as "1", if not =0, then a tag of "0". Excel is identifying blank cells as though they contained a zero and therefore tags these blank cells as a "1" when I need them to be reflected as a "0". Can anyone help / advise how to correct this. Thanks + happy new year |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula miscalculating blank cells
On Sun, 30 Dec 2007 16:57:00 -0800, Rob in Sydney <Rob in
wrote: I am trying to create a conditional formula that will tag cells with data points =0 as "1", if not =0, then a tag of "0". Excel is identifying blank cells as though they contained a zero and therefore tags these blank cells as a "1" when I need them to be reflected as a "0". Can anyone help / advise how to correct this. Thanks + happy new year Pretty sure this will work: =MAX(0,SIGN(A10)) --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula miscalculating blank cells
"Ron Rosenfeld" wrote in message
... On Sun, 30 Dec 2007 16:57:00 -0800, Rob in Sydney <Rob in wrote: I am trying to create a conditional formula that will tag cells with data points =0 as "1", if not =0, then a tag of "0". Excel is identifying blank cells as though they contained a zero and therefore tags these blank cells as a "1" when I need them to be reflected as a "0". Can anyone help / advise how to correct this. Thanks + happy new year Pretty sure this will work: =MAX(0,SIGN(A10)) --ron If the cell contains numeric 0, SIGN(0) = 0. =COUNT(A1)*(A1=0) -- Biff Microsoft Excel MVP |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula miscalculating blank cells
On Sun, 30 Dec 2007 22:57:36 -0500, "T. Valko" wrote:
If the cell contains numeric 0, SIGN(0) = 0. =COUNT(A1)*(A1=0) I knew that. Rereading the OP's request, I see I misread. I read 0 where he wrote =0. :-(( --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formats to ignore blank cells | Excel Worksheet Functions | |||
using conditional formatting - blank cells | Excel Discussion (Misc queries) | |||
How can I count conditional blank cells? | Excel Worksheet Functions | |||
Non-Blank Cells: Conditional Counting | Excel Worksheet Functions | |||
Conditional formatting blank cells | Excel Discussion (Misc queries) |