Conditional Formatting Question

[TMILJVIPM]

Im having a problem with conditional formatting on one of my cells. I can get it to work in my other cells that have a subtraction formula but not my cell with an average. Here's what I have:

Cell Y15 is for gross margin percentage and the formula that I have in the cell is =if(n(x15),x15/u15*100,"") now what I need from the conditional formatting is when cell Y15 is above 15% gross margin then I want a green icon, when it gets below 15% I want the yellow icon to show, and ofcourse when I fall below 0% the red icon. Now i've been able to get this like I sated in my other cells but not this one with the average formula.

Any help will be appreciated

 

[TMILJVIPM]

Anyone???

 

[Aladin Akyurek]

Im having a problem with conditional formatting on one of my cells. I can get it to work in my other cells that have a subtraction formula but not my cell with an average. Here's what I have:

Cell Y15 is for gross margin percentage and the formula that I have in the cell is =if(n(x15),x15/u15*100,"") now what I need from the conditional formatting is when cell Y15 is above 15% gross margin then I want a green icon, when it gets below 15% I want the yellow icon to show, and ofcourse when I fall below 0% the red icon. Now i've been able to get this like I sated in my other cells but not this one with the average formula.

Any help will be appreciated

The formulas to invoke in CF...

=ISNUMBER($Y15)*($Y15 >= 0.15)

for green;

=ISNUMBER($Y15)*($Y15 >= 0)*($Y15 < 0.15)

for yellow;

=ISNUMBER($Y15)*($Y15 < 0)

for red.

 

[TMILJVIPM]

The formulas to invoke in CF...

=ISNUMBER($Y15)*($Y15 >= 0.15)

for green;

=ISNUMBER($Y15)*($Y15 >= 0)*($Y15 < 0.15)

for yellow;

=ISNUMBER($Y15)*($Y15 < 0)

for red.

Im getting an error stating that I cannot use relative references in conditional formatting criteria for icon sets

 

[Jerry Sullivan]

Im getting an error stating that I cannot use relative references in conditional formatting criteria for icon sets

Since the CF is based on the value of Cell Y15, the there shouldn't be a need for any formulas.

The formula you are using in Y15 has already multiplied the gross margin % by 100, so 20% Gross Margin will appear as 20 in Cell Y15.
All your CF rules for the Icon Sets need to do is say:
Green >= 15 Type: Number
Yellow >=0 Type: Number

I'm guessing your problem might have been that you were using Type: Percent?

 

[TMILJVIPM]

Yes and you are correct. thanks I appreciate the help