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?

 

[DPChristman]

the problem is, I need something that I can do as a mass replace on. With almost 5000 cells to fix, I can't edit them one by one

 

[DPChristman]

When I replace
=IF(I8="","",SUM(K8/I8))
with
=AND(ISNUMBER(K8),K8>$K$2) - (I don't need the *1.3 it is already calculated in K2) I get a result of FALSE

 

[Snakehips]

I'm confused.
Surely, = IF(I8="","",K8/I8) is generating your percentage ? And I'm not suggesting you change that.

I'm suggesting that your conditional formatting formula be in the form =AND(ISNUMBER(??),??<??)
That is a single formula that will be applied to your desired cf range.

 

[FDibbins]

It seems we are dealing with 2 different issues here? If so, then I suggest we focus on 1 sat at time, maybe start with your CF problem?

 

[DPChristman]

Sorry, you have completely lost me. I see how telling the cf that the field needs to contain a number makes sense. But I have no idea how to do that, and the value of K8 is relative to the row that the formula is in.

 

[DPChristman]

I put together a more detailed explanation of the problem I am having. The references to row 8 are an example only, and the formula and formatting continues for approximately 1,250 rows.

Location of this formula is H8 - TOTAL SALES #
=IFERROR(VLOOKUP($C8,DATA!$A:$O,5,0),"")
This formula is saying that if the location does not exist in the source tab (DATA), return “”
This has to be done this way because there are also over 300 blank rows spaced between groups of locations (regions and districts). So, the formula cannot generate data in those rows.

Location of this formula is Q8 - NO SALE #
=IF(H8="","",(P8/H8))
Again, the result must return “”, because the formula will also exist in the blank rows.

So, what the formula accomplishes is the percentage of NO SALES to TOTAL SALES

The problem is that the CF is reading the resulting “” as greater than the number. As a result, all rows that are visibly blank (including the spacers between Regions and Districts), is being highlighted as greater than the number it is being compared to.

 

[Snakehips]

I still have to make some assumptions here so it is up to you to tweak the below as necessary in order to make it work with your set up.

Assuming : your data starts in row 8, ends in row 1,250, Cell A2 holds the number to compare with, you wish to highlight columns A thro Q

Maybe best remove any existing non-working cf that you have applied.

Select range A8:Q1250
Open cf dialog box >> New Rule >> Use a formula to determine which cells to format.
Edit the rule description >> =AND(ISNUMBER($H8),$Q8>$A$2)
Set your required format colour
Ok >> Ok
Job done!

If you want a hard value e.g. 0.3 rather than a reference to cell A2 then.... =AND(ISNUMBER($H8),$Q8>0.3)

 

[DPChristman]

I was actually able to come up with a fix.

One of the guys in my office showed me a workaround using the BETWEEN function, rather than the GREATER THAN/LESS THAN function.

Using that, instead eliminated the problem.

And best of all, because I was still using the "" option, I was able to copy it across blank fields without generating errors.

Thanks again, on to the next problem