Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero's
I have this formula that works very well.
=IF(ISBLANK(E297),IF(ISBLANK(C297),TEXT((D297-B297)/30.43,"#.0")&" (Plan-Plan)",TEXT((D297-C297)/30.43,"#.0")&" (Actual-Plan)"),TEXT((E297-C297)/30.43,"#.0")&" (Actual-Actual)") Problem is that many of the rows, do not have any values, and thus the formula yields "0.0 text". Is there a way to change this so when there is a 0 it returns a blank. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero's
I'd check for it first. Something like:
=if(count(yourrange)=0,"", IF(ISBLANK(E297),IF(ISBLANK(C297),TEXT((D297-B297)/30.43,"#.0")&" (Plan-Plan)",TEXT((D297-C297)/30.43,"#.0")&" (Actual-Plan)"),TEXT((E297-C297)/30.43,"#.0")&" (Actual-Actual)")) Regards, Fred "PAL" wrote in message ... I have this formula that works very well. =IF(ISBLANK(E297),IF(ISBLANK(C297),TEXT((D297-B297)/30.43,"#.0")&" (Plan-Plan)",TEXT((D297-C297)/30.43,"#.0")&" (Actual-Plan)"),TEXT((E297-C297)/30.43,"#.0")&" (Actual-Actual)") Problem is that many of the rows, do not have any values, and thus the formula yields "0.0 text". Is there a way to change this so when there is a 0 it returns a blank. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero's
Seems to give all blanks. What about using AND(ISBLANK(cell), ISBLANK....)
"Fred Smith" wrote: I'd check for it first. Something like: =if(count(yourrange)=0,"", IF(ISBLANK(E297),IF(ISBLANK(C297),TEXT((D297-B297)/30.43,"#.0")&" (Plan-Plan)",TEXT((D297-C297)/30.43,"#.0")&" (Actual-Plan)"),TEXT((E297-C297)/30.43,"#.0")&" (Actual-Actual)")) Regards, Fred "PAL" wrote in message ... I have this formula that works very well. =IF(ISBLANK(E297),IF(ISBLANK(C297),TEXT((D297-B297)/30.43,"#.0")&" (Plan-Plan)",TEXT((D297-C297)/30.43,"#.0")&" (Actual-Plan)"),TEXT((E297-C297)/30.43,"#.0")&" (Actual-Actual)") Problem is that many of the rows, do not have any values, and thus the formula yields "0.0 text". Is there a way to change this so when there is a 0 it returns a blank. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero's
You can if you want. But Count should also work, and is easier to specify
multiples ranges. Why don't you post the formula you tried so we can comment on it? Regards, Fred. "PAL" wrote in message ... Seems to give all blanks. What about using AND(ISBLANK(cell), ISBLANK....) "Fred Smith" wrote: I'd check for it first. Something like: =if(count(yourrange)=0,"", IF(ISBLANK(E297),IF(ISBLANK(C297),TEXT((D297-B297)/30.43,"#.0")&" (Plan-Plan)",TEXT((D297-C297)/30.43,"#.0")&" (Actual-Plan)"),TEXT((E297-C297)/30.43,"#.0")&" (Actual-Actual)")) Regards, Fred "PAL" wrote in message ... I have this formula that works very well. =IF(ISBLANK(E297),IF(ISBLANK(C297),TEXT((D297-B297)/30.43,"#.0")&" (Plan-Plan)",TEXT((D297-C297)/30.43,"#.0")&" (Actual-Plan)"),TEXT((E297-C297)/30.43,"#.0")&" (Actual-Actual)") Problem is that many of the rows, do not have any values, and thus the formula yields "0.0 text". Is there a way to change this so when there is a 0 it returns a blank. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zero's | Charts and Charting in Excel | |||
Zero's # 2 | Excel Discussion (Misc queries) | |||
Zero's | Excel Discussion (Misc queries) | |||
Leading zero's | Excel Discussion (Misc queries) | |||
Zero's | New Users to Excel |