Conditional formatting

[dillons]

I need some help with conditional formatting a series that has blank values.

I have a series say A1,A2,A3,A4,A5, and i want to apply a 3 color scale where green is the lowest value and red is the highest. The problem is i may not have a value in A2 & A3, This is throwing my lowest value off and the scale starts in red.

Is there a way I can ignore blank values in the scale?

 

[Joe4]

Welcome to the Board!

You could use the Formula option, and use:
=MIN(A1:A5) for the minimum
and
=MAX(A1:A5) for the maximum

The MIN and MAX function automatically ignore blank values.

 

[dillons]

Welcome to the Board!

You could use the Formula option, and use:
=MIN(A1:A5) for the minimum
and
=MAX(A1:A5) for the maximum

The MIN and MAX function automatically ignore blank values.

Thanks!

How would that formula work in a three scale format?

 

[Joe4]

How would that formula work in a three scale format?

I am sorry, it doesn't. It only shows Max and Min.

I have not used the Three Scale option much, but I was just messing around with it, and it seems to ignore the blank cells for me.
Are you saying it is not doing that for you?
Can you post an image for me (see: XL2BB - Excel Range to BBCode).

 

[dillons]

I am sorry, it doesn't. It only shows Max and Min.

I have not used the Three Scale option much, but I was just messing around with it, and it seems to ignore the blank cells for me.
Are you saying it is not doing that for you?
Can you post an image for me (see: XL2BB - Excel Range to BBCode).

Is there a way to upload a file?

 

[Joe4]

No. If you want to share a file, you would need to upload it to a File Sharing site, like DropBox, and provide the link here.
Note that many people will not download files for security reasons.
I myself cannot download it while at work, as my work forbids the downloading of files. I can do it from my home computer, but won't be there until this evening.

 

[dillons]

J12:J16 is with normal 3 scale color
L12:L16 is normal scale but with blank rows. I need that 1 to be green because it is the only one
R12:R16 has 2 formula driven formats a =min in green and a =max in red. As you can see it is all red.
Another problem I have is i have to do this in every series between the black lines and every column. Im trying to get a visual clue to the lowest bid in every catagory.

 

[Joe4]

I cannot reproduce your results.

For the situation when there is just one entry, it looks like Excel makes it the "MAX" not the "MIN". If you want it to behave like this MIN, keep your 3-Scale Conditional Formatting, but then add another Conditional Formatting rule, and set it as the highest priority (over the 3-Scale one). That rule should look like this (for Range J12:J16):
=AND(COUNT(J$12:J$16)=1,J12<>"")
and then choose the green color.

So for situations where there is just one number, it will override the 3-Scale rule and show green.

Since you want 3-Scale, I would get rid of the rule you are using for column R and use these same two rules on each range.

If you are seeing any "blank" columns being shaded, it probably means that they really are not blank, they have a space or something else in them. If you get rid of those, it should work.
If those cells have formulas in them, please post what the formulas look like.