Conditional Formatting range of values

[missy0107]

Hello! I am an Excel novice and any help is appreciated!

I'm creating a spreadsheet to track goals for my team. I'm using conditional formatting to change the color of a cell based on a range of values rounded to two decimals. The problem I'm running into is that when you are on the edge of two ranges and it rounds up, the value on the backend (unrounded) is below the range. How do I get Conditional Formatting to recognize the rounded number instead of the backend value?

[Pete_UK]

Use a formula for the CF condition, like this:

=ROUNDUP(A1,2)=value

assuming it is cell A1 that the CF is applied to - put the appropriate value in there.

Hope this helps.

Pete

[missy0107]

I think it does, just a follow up question. What happens when your value is a range?

=ROUNDUP(A1,2)=VALUE
=ROUNDUP(A1,2)=0.01-4.99?

[missy0107]

Here's some more detail.

I have 5 Ranges of Values with corresponding colors

6.90-7.00 - Dark Green
6.40-6.89 - Light Green
6.10-6.39 - Yellow
5.50-6.09 - Orange
0.01-5.49 - Red

Blank - No color (figured I solved this by making the bottom of the red range 0.01)

I applied =ROUNDUP(A1,2)=0.01-4.99 formula to my conditional formatting for each range, but nothing changed color.

[Pete_UK]

You need to apply your conditions with a formula like this:

=AND(ROUNDUP(A1,2)>=0.01,ROUNDUP(A1,2)<5.5)

for red, and your next one would be:

=AND(ROUNDUP(A1,2)>=5.5,ROUNDUP(A1,2)<6.1)

for orange, and so on.

Hope this helps.

Pete

[missy0107]

Thanks, that worked!!

[Pete_UK]

Glad to hear it.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete