Condtional Format cells - Format for cells that are not 0%

[MrPink1986]

HI,

I would like to format a number of ranges on my spreadsheet. The format is to be a colour when the value is not 0%. My problem arises that there is a formula in the range and when the format is applied any cells with a formula is applied, I would like the format only to bel applied when there is an actual value and not a formula.

This is the formula I have in my spreadsheet - =IF(A3="","",P3/H3)

Any help greatly appreacited.

Cheers
S

 

[Andrew Poulsom]

Why can't you use this formula?

=IF(A3="",0,P3/H3)

You can suppress the display of zero with a number format like:

0.00%;-0.00%;

The key is the trailing semicolon.

 

[MrPink1986]

Why can't you use this formula?

=IF(A3="",0,P3/H3)

You can suppress the display of zero with a number format like:

0.00%;-0.00%;

The key is the trailing semicolon.

Thanks for the reply.

I dont follow - I have my formula set so the cell is balnk unless there is a item in A. When I format the formula is read and it doesnt correctly apply the formatting.

 

[Andrew Poulsom]

I don't follow that, sorry. I merely changed your formula so that it returned zero instead of "".

 

[MrPink1986]

I don't follow that, sorry. I merely changed your formula so that it returned zero instead of "".

OK thanks - I want to apply condtional formatting to a range in coumn Z. For all instances where the value is not zero I want the cell to be red. I have changed my formula to yours above however the formatiing is not correct as it reads the actual formula written as a value and not zero therefore leaving red.

 

[Andrew Poulsom]

Do you mean you see the formula in the cell rather than its result? If so make sure that the cell is formatted as other than Text before entering the formula.

 

[MrPink1986]

Do you mean you see the formula in the cell rather than its result? If so make sure that the cell is formatted as other than Text before entering the formula.

Hi,

Nope - in the cell I see balnak as I have the iferror retunring "". I have pasted the table below (doesnt show colour not as useful as I wanted it to be) For everything that is greater than 0.00% it is formatted in a colour. Below the data the formula is present however showing "" this however is still formatted in the colour as if it is greater than 0.00%

I want cells that are blank to have no formatting though as there is a formula in it it is reading that this is greater than 0.00% and applying the colour.

[TABLE="width: 285"]
<TBODY>[TR]
[TD="align: right"]0.00%</SPAN>[/TD]
[TD="align: right"]0.00%</SPAN>[/TD]
[TD="align: right"]0.00%</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]0.00%</SPAN>[/TD]
[TD="align: right"]0.00%</SPAN>[/TD]
[TD="align: right"]0.00%</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]0.00%</SPAN>[/TD]
[TD="align: right"]0.00%</SPAN>[/TD]
[TD="align: right"]0.00%</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]0.00%</SPAN>[/TD]
[TD="align: right"]0.00%</SPAN>[/TD]
[TD="align: right"]0.00%</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]0.00%</SPAN>[/TD]
[TD="align: right"]0.00%</SPAN>[/TD]
[TD="align: right"]0.00%</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]0.00%</SPAN>[/TD]
[TD="align: right"]0.00%</SPAN>[/TD]
[TD="align: right"]0.00%</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]0.00%</SPAN>[/TD]
[TD="align: right"]0.00%</SPAN>[/TD]
[TD="align: right"]0.00%</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]0.53%</SPAN>[/TD]
[TD="align: right"]17.02%</SPAN>[/TD]
[TD="align: right"]1.07%</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]3.31%</SPAN>[/TD]
[TD="align: right"]0.00%</SPAN>[/TD]
[TD="align: right"]3.20%</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]3.43%</SPAN>[/TD]
[TD="align: right"]4.49%</SPAN>[/TD]
[TD="align: right"]0.00%</SPAN>[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

 

[Andrew Poulsom]

So you haven't used the formula and number format that I suggested in Post #2?

 

[MrPink1986]

I changed the formula to return zero and same result could you explain further the second part "You can suppress the display of zero with a number format like:

0.00%;-0.00%;"

Where should the semicolon be placed - in a formula on the condtional fomratting?

 

[Andrew Poulsom]

You right click, choose Format Cells and on the Number tab select Custom and enter this in the Type box:

0.00%;-0.00%;