Conditional Format Issues

[DPChristman]

I have conditional formatting set up on a large spreadsheet. In looking for exceptions above a given threshold, the formula basically says highlight examples where a given location is greater than 30 % higher than the average (greater than X*1.3). This conditional formatting seems to work well on some columns. Yet in other columns it highlights everything, including blank cells.
Since some of the locations do not have any data, I used this formula to eliminate errors and N/As


=IF(I8="","",SUM(G8/I8))

= IF ( I8 = "" , "" , SUM ( G8 / I8 ) )


Any idea what I am doing wrong?

 

[FDibbins]

1st, CF rules only work on TRUE or FALSE (1 or 0), so you only need to structure your formula to 1 of those.
2nd, you don't need to use SUM() for a calc like that, you would use SUM when adding up a series of numbers
so you would only need, in your example, =IF(I8="","",G8/I8)

You did not give your CF rule, but it would be something like...
=$A2>$B2*1.3
where column A would contain the value to test, and column B would contain your criteria
Note the use of the $ sign, that locks the specific column so it doesn't adjust when applied across other columns

Make sure that your Applies To range covers all the data you want the rule applied to

 

[Norie]

If you have "" in a cell it will always be considered greater than a number by Excel.

You should probably add a check for "" in your conditional formatting formula.

Not sure about the other cells apparently being wrongly, can you give some examples?

 

[Snakehips]

DPC,

Maybe conditional formula similar to below example.

Excel Workbook
	G	H	I	J	K
4				Average >>	273.21%
5
6
7
8	2		4		50.00%
9	5		7		71.43%
10	34		7		485.71%
11
12	34		7		485.71%
13
14
Sheet5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K8	1. / Formula is =AND(ISNUMBER(K8),K8>$K$4*1.3)	Abc

Hope that helps.

 

[DPChristman]

Yes, I just noticed that I don't have this issue with negative values, and the CF is less than. It is only on the positive values. Is there an easy way to fix this? I have 4964 cells with this formula in it that is generated the bad highlighting

 

[FDibbins]

Did you check the Applies To range?

 

[DPChristman]

...over my head

 

[FDibbins]

...over my head

Was that directed at me?

If so, then no it is not. When you 1st open the CF window, on the right is the range that the rule is being Applied To. Just make sure it covers your working range

 

[DPChristman]

Sorry, ok, yes I see what you mean now. Yes.
In this case the working range is =$Q$8:$Q$1247
I think if having "" in the cell always makes the value greater than a number that is the issue.
I use the same CF with less than for negative values in adjacent columns, and that is not a problem. The highlighting works perfectly there. Just don't know how to fix it.

 

[Snakehips]

Sorry, ok, yes I see what you mean now. Yes.

I think if having "" in the cell always makes the value greater than a number that is the issue.
I use the same CF with less than for negative values in adjacent columns, and that is not a problem. The highlighting works perfectly there. Just don't know how to fix it.

Have you not tried my suggestion of =AND(ISNUMBER(Bla),etc) ??
That should get around the "" issue.