Excel Help

[keggerz]

I have 32 rows and one column has numbers that range from 36 to 64(some numbers are repeated)...I want to highlight all the cells that are 51 or greater in Green, cells that are 49 or less in Red, and any cell with the exact number 50 in Yellow...I did it with conditional formatting but for some reason one of the 50's is yellow and the other is orange....then in the 51 and up all are the color green that i picked but one cell has a completely different color green...then I have one 50 cell that is yellow and another that is orange.

 

fwiw I am using Conditional Formatting>Color Scales>3-color scale...then using "number" and picking the color that I want for each.

 

oh and it's Excel 2007

Edited September 18, 2012 by keggerz

[Big Country]

Is it possible that the numbers are not integers, but may be something like 49.5 that displays as a 50 (and if the scale is set to 49 and below to one color and 50 exactly as a color, it is not fitting into a defined rule?

 

now, not sure why the random shading differences without seeing the actual file and the way the rules are written. I would only guess that perhaps if these are not integers it is causing something funky with the way the rules are written for the formatting.

[keggerz]

Is it possible that the numbers are not integers, but may be something like 49.5 that displays as a 50 (and if the scale is set to 49 and below to one color and 50 exactly as a color, it is not fitting into a defined rule?

 

now, not sure why the random shading differences without seeing the actual file and the way the rules are written. I would only guess that perhaps if these are not integers it is causing something funky with the way the rules are written for the formatting.

 

that is probably it...week 1 the numbers were the raw numbers...week 2 they are of course divided by two...so I bet that is it...of course I don't know how to fix it but I do believe that is it. Edited September 18, 2012 by keggerz

[Big Country]

that is probably it...week 1 the numbers were the raw numbers...week 2 they are of course divided by two...so I bet that is it...of course I don't know how to fix it but I do believe that is it.

 

 

you'll basically need 3 ranges - less than 50, = to 50, and greater than 50. That should cover you.

 

I still don't know about the random shading of the cells, could there be other conditional formatting rules being applied?

[lennykravitz2004]

I would try re-selecting the range of data, then "Manage Rules" to see what rules are in place for your range of data. If needed, create a second copy of the sheet to "mess up". Then delete the current rules and start over.

 

What I would do is create rules based on a formula, and create a formula for each condition. This probably involves tinkering with the ordering rules for formatting, but should be the best solution.

 

Will 50 always be the magic number, or will that mid-point number change from week to week?

 

ETA: the shading of different color greens is due to the color scale being used. If you look at the bottom of the window where you set the Cond Form rules, you'll see a "sliding scale" for color being displayed, showing how the colors will display depending on where they are in the range.

 

ETA - 2: And this one, I DO know.

Edited September 19, 2012 by lennykravitz2004

[keggerz]

I would try re-selecting the range of data, then "Manage Rules" to see what rules are in place for your range of data. If needed, create a second copy of the sheet to "mess up". Then delete the current rules and start over.

 

What I would do is create rules based on a formula, and create a formula for each condition. This probably involves tinkering with the ordering rules for formatting, but should be the best solution.

 

Will 50 always be the magic number, or will that mid-point number change from week to week?

 

ETA: the shading of different color greens is due to the color scale being used. If you look at the bottom of the window where you set the Cond Form rules, you'll see a "sliding scale" for color being displayed, showing how the colors will display depending on where they are in the range.

 

ETA - 2: And this one, I DO know.

 

50 is always the the mid-point.

[SEC=UGA]

Just get some highlighters and mark them on the screen.

[lennykravitz2004]

1) Delete the current rules for the range of data. Just start from scratch, it'll be easier.

2) Put the value of 50 in cell A1 (for example. or use whatever cell is convenient for you.) This will help should you ever need/want to change your midpoint. Update this one cell instead of all the formulas we're about to make for the formatting rules.

3) From the Cond Formatting menu, choose "New Rule", then "Use a formula..." (should be last in list from top)

4) In the blank line for the formula, create one similar to this:

=$AE18:$AE23>$A$1 With $AE18:$AE23 representing your range of data to compare the cell A1 (i.e. 50) against. And make sure the dollar signs are in front of the column, but NOT the row number.

5) Set the format fill color to red. These are your "over 50" values.

6) Create a new rule just like above, now for those equal to 50, and set to yellow fill.

=$AE18:$AE23=$A$1

7) Create the last new rule just like the ones above, now for those less than 50, and set to green fill

=$AE18:$AE23<$A$1

8) When I did my example, I had them ordered (by color/formula) as red, then yellow, and green last at the bottom.

 

Also, I would consider formatting the numbers in the column to at least one decimal displayed. As pointed out, some of these values will show in different colors because an exact value of 50 and rounded value of 49.5 will both display as the number 50 (if no decimal), but be colored differently due to "exact" differing decimal value.

 

Hope this all makes sense. If not, PM me where to send, and I can send you my sample with (hopefully) clearer instructions.

[gbpfan1231]

1) Delete the current rules for the range of data. Just start from scratch, it'll be easier.

2) Put the value of 50 in cell A1 (for example. or use whatever cell is convenient for you.) This will help should you ever need/want to change your midpoint. Update this one cell instead of all the formulas we're about to make for the formatting rules.

3) From the Cond Formatting menu, choose "New Rule", then "Use a formula..." (should be last in list from top)

4) In the blank line for the formula, create one similar to this:

=$AE18:$AE23>$A$1 With $AE18:$AE23 representing your range of data to compare the cell A1 (i.e. 50) against. And make sure the dollar signs are in front of the column, but NOT the row number.

5) Set the format fill color to red. These are your "over 50" values.

6) Create a new rule just like above, now for those equal to 50, and set to yellow fill.

=$AE18:$AE23=$A$1

7) Create the last new rule just like the ones above, now for those less than 50, and set to green fill

=$AE18:$AE23<$A$1

8) When I did my example, I had them ordered (by color/formula) as red, then yellow, and green last at the bottom.

 

Also, I would consider formatting the numbers in the column to at least one decimal displayed. As pointed out, some of these values will show in different colors because an exact value of 50 and rounded value of 49.5 will both display as the number 50 (if no decimal), but be colored differently due to "exact" differing decimal value.

 

Hope this all makes sense. If not, PM me where to send, and I can send you my sample with (hopefully) clearer instructions.

 

Why not round the numbers to zero decimals and then you don't have to worry about the decimals?

[lennykravitz2004]

Why not round the numbers to zero decimals and then you don't have to worry about the decimals?

 

 

Doesn't work. "Assuming" you mean use =round(cell #, # of decimals to round) formula. Excel still sees the exact value. At least it did in my example i tried. I could very well be wrong and not understand what function you are referring to use for this though...

[gbpfan1231]

Doesn't work. "Assuming" you mean use =round(cell #, # of decimals to round) formula. Excel still sees the exact value. At least it did in my example i tried. I could very well be wrong and not understand what function you are referring to use for this though...

 

Interesting - I am new to the new version of Excel and maybe in formatting Excel still sees the exact value but it does not in other circumstances. For example do this:

 

A1 - =33/2 - result is 16.5

B1 - =ROUND(A1,0) result is 17.000

C1 - =IF(B1<17,1,0) result is 0 - this emans Excel see the number as 17.00 not still the exact value - would be surprised if conditional formatting acts different???

[lennykravitz2004]

Interesting - I am new to the new version of Excel and maybe in formatting Excel still sees the exact value but it does not in other circumstances. For example do this:

 

A1 - =33/2 - result is 16.5

B1 - =ROUND(A1,0) result is 17.000

C1 - =IF(B1<17,1,0) result is 0 - this emans Excel see the number as 17.00 not still the exact value - would be surprised if conditional formatting acts different???

 

 

that is weird - b/c it does act differently. I'm in Office 2010, but that should be (for our intents and purposes) the same. Your formula does work as you describe, but the conditional formatting works "differently" as I've tried to describe. When I set a value to 49.9999999, my sheet auto-colored it differently than the exact value of 50(???). huh.

[Big Country]

Lenny - in my sample sheet that I setup, the conditional formatting is viewing the cell with a rounded formula in it converting 49.5 to 50 as a 50 for conditional formatting purposes.

 

I changed your conditional formatting formulas as you don't need to enter the range in the formula bar, should really only do one cell and then identify the range in the separate box for the applies to range. I kept running into errors when I was using the range in the formula.

 

So, my 3 formulas are (my data is in Column A starting at row 5, cell A1 has "50" entered into it):

=A5<$A$1 - shaded red - Applies to =$A$5:$A$33

=A5=$A$1 - shaded yellow - Applies to =$A$5:$A$33

=A5>$A$1 - shaded green - Applies to =$A$5:$A$33

 

 

I then duplicated these rules after adding 0.5 to each integer (my data was each integer from 36 to 64) and it functioned perfectly (no yellow as no cell was equal to 50) and then in a third column I entered the Round formula for each of the values with the .5 ending and it again functioned properly (the cell with 49.5 has a rounded value displayed as 50 and that was shaded yellow).

 

This is in Excel 2010, so possible there are slight variations from 2007.

 

 

Steve - the reason you see differeing shades of the colors, as lennykravitz noted above, is from the way you set up the conditional formatting using a "scale"

[lennykravitz2004]

Lenny - in my sample sheet that I setup, the conditional formatting is viewing the cell with a rounded formula in it converting 49.5 to 50 as a 50 for conditional formatting purposes.

 

Dude... I am just not seein' it work for me.

 

What I found to work for me was a setting for the workbook in File>Excel Options (at bottom)>Advanced>When Calculating This Workbook>checkbox for "Set Precision as Displayed". Check that box, click OK on the scary prompt about "data loses accuracy". This in turn made all of my displayed (rounded) 50 values yellow. Now, I will say that because of the prompt about accuracy, I went back and copied the sheet into a new, blank workbook to try it all out again in case something went haywire and left my original file alone.

 

I just could not get your example to work for me as you described. Not saying it's incorrect by any means, as I am very well aware there are about 5 or 6 ways to copy and paste in Excel too (i.e. "skin a cat").

 

Here's the thread I found the answer from, and this is what I think sums up my perspective on it:

 

http://www.mrexcel.com/forum/excel-questions/28664-conditional-formatting-rounding.html

 

Your cell has the value 7.77 formatted to zero decimal places to displaye 8. Formatting is not the same as rounding - the underlying value is still 7.77. So 10 minus 7.77 is 2.23, which is more than 2.

 

[keggerz]

Lenny - in my sample sheet that I setup, the conditional formatting is viewing the cell with a rounded formula in it converting 49.5 to 50 as a 50 for conditional formatting purposes.

 

I changed your conditional formatting formulas as you don't need to enter the range in the formula bar, should really only do one cell and then identify the range in the separate box for the applies to range. I kept running into errors when I was using the range in the formula.

 

So, my 3 formulas are (my data is in Column A starting at row 5, cell A1 has "50" entered into it):

=A5<$A$1 - shaded red - Applies to =$A$5:$A$33

=A5=$A$1 - shaded yellow - Applies to =$A$5:$A$33

=A5>$A$1 - shaded green - Applies to =$A$5:$A$33

 

 

I then duplicated these rules after adding 0.5 to each integer (my data was each integer from 36 to 64) and it functioned perfectly (no yellow as no cell was equal to 50) and then in a third column I entered the Round formula for each of the values with the .5 ending and it again functioned properly (the cell with 49.5 has a rounded value displayed as 50 and that was shaded yellow).

 

This is in Excel 2010, so possible there are slight variations from 2007.

 

 

Steve - the reason you see differeing shades of the colors, as lennykravitz noted above, is from the way you set up the conditional formatting using a "scale"

 

I've tried to not use a scale but can't figure out how.

[Big Country]

I've tried to not use a scale but can't figure out how.

 

 

When you go to Conditional Formatting -. New Rule, don't use the default option which is "Format all cells based on their values". You need to select "Use a formula to determine which cells to format".

[Big Country]

Dude... I am just not seein' it work for me.

 

What I found to work for me was a setting for the workbook in File>Excel Options (at bottom)>Advanced>When Calculating This Workbook>checkbox for "Set Precision as Displayed". Check that box, click OK on the scary prompt about "data loses accuracy". This in turn made all of my displayed (rounded) 50 values yellow. Now, I will say that because of the prompt about accuracy, I went back and copied the sheet into a new, blank workbook to try it all out again in case something went haywire and left my original file alone.

 

I just could not get your example to work for me as you described. Not saying it's incorrect by any means, as I am very well aware there are about 5 or 6 ways to copy and paste in Excel too (i.e. "skin a cat").

 

Here's the thread I found the answer from, and this is what I think sums up my perspective on it:

 

http://www.mrexcel.c...g-rounding.html

 

 

 

Are you formatting your cells to display no decimal places or are you using the actual "ROUND" function? I am using the ROUND function to get the result I describe, and have done it two ways. One was to put the ROUND formula in a separate column and conditionally format that, thus the display shows 50 (49.5 is value in cell B18, cell C18 is =ROUND(B18,0) and displays a 50) and is shaded yellow.

 

Now, if my cell displays a 49.5, and I put the ROUND function into the conditional formatting function, the display does not change, but anything that would round to 50 will now shade yellow, in this case, my cell value is 49.5, and I have the following conditional formatting rules in place:

 

=ROUND(A5,0)<$A$1 - shaded red - Applies to =$A$5:$A$33

=ROUND(A5,0)=$A$1 - shaded yellow - Applies to =$A$5:$A$33

=ROUND(A5,0)>$A$1 - shaded green - Applies to =$A$5:$A$33

 

My cell with the value of 49.5 in it displays a 49.5 but is shaded yellow

 

Now, if i then additionally format the cells to display 0 decimal places, my cell with a value of 49.5 now displays a 50, but the conditional formatting does not change (ie, the column where I have included the ROUND in the conditional formatting function is shaded yellow, the column where I used my original formulas is still shaded green)

 

 

 

Keg - All this said, comes down to how you want the numbers to be treated - truly only values = to 50 highlighted yellow, or anything that would round to 50 highlighted yellow, or something different?