Excel 2010 Conditional Formatting

[CharlieT302]

Hi All,

This is a simple, but frustrating question. I have a sample spreadsheet with cells numbering from 1-100.

I applied conditional formatting using the Icon set with arrows and the default percents. Since I was only testing, I used 1-100 to have each value correspond to the correct percentage (5=5%, 10=10%, etc.).

Default Percent Formats:
>=75 Green Up Arrow
>=50 and <75 Yellow Slant Up Arrow
>=25 and <50 Yellow Slant Down Arrow
<25 Red Down Arrow

When simply numbering 1-100, 75 and up (>=75) "should" be Green Up arrow. However, this only happens if the value is 76.
It appears to be following the rule >75 rather than >=75

When working with "real" data, it gets worse. I had to change a cell to a value that is equal to 79% before it displayed a Green Up arrow.

How Does Excel Figure The Percentages:
I assumed, that Excel used the highest value in the defined cell range to represent the 100% value, and then determined the percentage of all other values in relation to the high value cell. Clearly, this is not the case.

Does anyone know what logic Excel is using?

 

[SkipVought]

hi,

I have a sample spreadsheet with cells numbering from 1-100

BTW, your cells are not numbered, your cells contains VALUES from 1 to 100.

You are really talking about NUMBERS not PERCENTS. Just change the TYPE in the CF Edit.

But even using percents, the logic misses at the boundary. I'd avoid using pct!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CharlieT302]

Hi Skip,

I appreciate the input. However, I am actually talking about percents and not numbers. Let me explain again, in case I was unclear.

1.) I numbered cells 1-100 (one hundred cells in all). I did this as a simple test so that each cell value (number) equates to a percentage (1 = 1%, 26=26%, 67=67%, etc.).

2.) I then applied a conditional format to the entire range of cells using the Icon set with the default conditional formats, which applies formats based upon a cell's relative percentage to "some" high value.

Based on that default format, cells that are 75% of the high value are formatted with a Green Up Arrow.

My question is:
How does Excel determine its High value of 100 % when calculating how far off each cell is from that mark? Clearly it is not simply the highest value in the range, as that produced incorrect results.

I am not asking for an alternative, but rather an understanding of Excel's logic. The answer has to be something more than Excel is quirky. There must be a defined logic that I am missing.

Has anyone else experienced this or know what Excel is doing?

 

[SkipVought]

1 does not equal .01!
26 does not equal .26!
...
I already gave you your answer, to change the TYPE from PERCENT to NUMBER

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CharlieT302]

Again Skip, thank you for your reply, but you did not give me my answer to my question. Though I appreciate your input.

The number 1 absolutely does equal 1% of 100

I am not asking for a solution to a problem or a workaround. I am asking a conceptual question. The question is:

In a range of cells, with respect to conditional formatting, how does Excel determine the High value. For it is this value that must be used to determine the relative percentage of another cell's value.

I appreciate anyone's input on this matter.

 

[mintjulep]

I see what you mean. Behavior is not as expected, although I'll admit that I'm not sure what to expect.

Green up arrow when value is >= 67 percent.

Percent of what?

Help is not helpful.

Use a percentage when you want to visualize all values proportionally because the distribution of values is proportional.

Proportional to what?

 

[SkipVought]

I'd avoid using pct

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Hoaokapohaku]

What am I missing here? Seems to me this problem has nothing to do with percentages. The spreadsheet contains integers ranging from 1 to 100. Conceptually, these integers are intended to represent decimal values equal to the integer divided by 100 - a very common practice - but they are not actually decimals, they are just whole numbers. The conditional formatting criteria also refers to values in relation to integers in the same value range as the integers that are actually in the worksheet. Therefore, percents and/or number formatting have nothing to do with this problem. If you change the type to number as Skip suggested, your conditional formatting will work as you seem to expect. In the attached workbook, the table on the left illustrates this.

Regarding, "In a range of cells, with respect to conditional formatting, how does Excel determine the High value? For it is this value that must be used to determine the relative percentage of another cell's value." I have NO IDEA! "Excel is quirky" is definitely not the correct answer, but it might as well be since the way Excel applies the formatting seems to defy any logic. I'm sure it does follow some logic, but I'm also quite sure the logic is in error. If you play around with the table on the right in the attached spreadsheet, I think you'll see why I say that. Start deleting the highest values in the table one by one, and look at the calculated percent of the maximum value in the table in the values below the table, comparing them to the value at which the icon changes for a cell. Not only do they not change at the correct value, they don't even change at the same degree of error.

I think the only answers to this problem is MS fixes their bug, or don't use the percent type when applying conditional formatting. Incidentally, the same problem exists with the percentile type as well.

Whatever this problem actually is, I wonder what other aspects of conditional formatting are affected? I dove into this thread because I have some ideas in mind to use Excel 2010 conditional formatting, and I thought researching this problem would help educate me on its features. After spending way to much time trying to figure this out, I now have much less confidence in the product and may not try to implement the ideas I had in mind.

 

[SkipVought]

Well go figger, as we say in Texas!

Make your list of numbers 0 - 100 (in other words 101 VALUES).

NOW the icon displays as the OP expects, using the PERCENT TYPE.

So the implication is that the PERCENT type has nothing to do with the value alone, but is related to the value and the total count of values and that can be illustrated by deleting the last 5 to 10 rows and observe how the icons change.

So be very careful how you use PERCENT, unless you really understand what will happen as values & count of values change.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mintjulep]

So it appears to NOT be

=IF(PERCENTRANK(Range,Value)>=Conditional_Limit,"Green Up Arrow")

 

[SkipVought]

@mint

Depends if you have 100 or 101 distinct values to 100, beginning with either 1 or 0!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Hoaokapohaku]

Here's another strange behavior I didn't mention in my previous post. Select the range of the conditional formatting, edit the type and change it (doesn't matter to what), click on any random unused cell, enter a value. The range of the conditional formatting will be changed to include the randomly included cell. I don't often find anything in Excel that catches me by surprise anymore, but this does. Anyone have an explanation?

 

[Hoaokapohaku]

Ok, I think I have this figured out!

It seems the intent of the percent type is to evaluate a cell as a percentage of the difference between the maximum and minimum range of values found in a range of cells. So, if a cell contains the value "10", and the entire range has a maximum value of 100 and a minimum value of 5, the conditional formatting should be evaluated by the value 10/(100-5) = 10/95 = .1052. So if the criteria were >= .1, it would evaluate as TRUE and the conditional formatting would be applied.

I say the above is how it SHOULD be evaluated, but I believe there is an algebraic error in the conditional formatting programming. A cell in question should be evaluated "=CellEvaluated/(MAX(RangeEvaluated)-MIN(RangeEvaluated))" but it actually is evaluated "=CellEvaluated/MAX(RangeEvaluated)-MIN(RangeEvaluated)." Note the missing set of parenthesis in the divisor part of the formula.

I attached a revised spreadsheet. This time below the table of values conditionally formatted with the "Percent" type, I entered tables that evaluate the criteria condition both with the apparent algebraic error, and with corrected algegra, and conditionally formatted them to shade the cell colors per Charlie's original criteria. The table with the corrected algebra gives the expected result in all cases. The table with the algebra in error gives the same result as the table conditionally formatted using the "percent" type.

Back to Charlie's original problem. The percent type should not be used both because it's purpose is different than what Charlie wants, and because even if it was what he wanted, it gives errant results. The number type will give the correct results and will format as Charlie wants.

The percent or percentile types should not be used ever unless precision in the formatting applied doesn't matter, or until MS fixes it. If the functionality intended in the "percent" type is wanted, it must be implemented using formulas for criteria.

 

[lionelhill]

I'm sorry, people, I can't find the problem here. Is this something version-specific? On my copy of Excel2007, using whatever data I've put into 100 successive cells, with the 4-arrow icons in percent mode, it quite clearly evaluates the position of each value on a scale of (lowest-value = 0%) to (highest-value = 100%), and marks arrows appropriately. That's exactly what I expected it to do.

This works quite happily if I delete some cells, or add extras.

I can't believe the missing parenthesis thing because if I have a set of values between 1000 and 1050, then all percentages calculated using the formula (value/max)-min instead of value/(max-min) would be hugely negative, and the whole thing would break down, but it doesn't.

I hate to say it, but at least in the version of Excel I'm using, Microsoft seem to have got it right.

 

[Hoaokapohaku]

@lionelhill - I suggest you look at the attached worksheets in the posts above, looking carefully at the margins of the criteria. It does not work correctly. The problem is duplicated by all the other participants in this thread. I am using 2010 version. There are also various other forums that document the exact same discrepancy, with no solution or explanation in any of them. Also regarding the negative numbers, you are using errant algebra when you arrive at the negative values. Correct algebraic order does not produce negative values.

 

[CharlieT302]

Hi All,

I have read all the comments here and appreciate everyone's input as I was (and still am) desperately seeking an answer.

As people in this post seem to concur, the Percent with the Icon Set does not seem to work. Obviously, Excel must be using some logic to come up with an answer; even an incorrect one. I just do not know what it is. If anyone has figured it out, please post it.

There are actually two aspects to this post. First, what calculation is Excel actually doing because what is stated in Help is clearly not correct. Second, how to format percents with the Icon Set. Respectfully, I cannot tell a client to simply not do percentages or to add extra digits to acual work history in hopes of getting the formatting to work.

For those who simply need to be able to calculate percentage contributions, I have been able to get the Icon Set to format correctly based upon percentages. The method I used was to switch the Type to "Formula" and base the formatting on a formula that calculates the percentage contribution of each cell to the maximum value of the range. So, for what it's worth...

Work Around
>=75 Green Up Arrow
>=50 and <75 Yellow Slant Up Arrow
>=25 and <50 Yellow Slant Down Arrow

Specifying Formula as the Type
>=MAX($B$5:$E$10)*0.75
>=MAX($B$5:$E$10)*0.50
>=MAX($B$5:$E$10)*0.25

It's not an answer to the original question, but at least it will format.