# Rounding formula when using conditional formatting?

S

#### SunshineinFt.Myers

I have spreadsheet that has conditions and depending on those conditons the
cells changed color. For example: if cell is less than .29 or = to .29 turn
the cell red. Now this cell that has the actual number is next to the cell
that shows the % and then the color and the conditional cell has a formula in
it. =B12/\$B\$10. Example: Cell B12 =54 and cell B10 has a total formula thus
giving me the number 39. How can I put a formula in the cell that already has
the =B12/\$B\$10 that will round the number up say from .46 to .05 thus
updating my color? See right now it does not change to the next color even
though I decrease the decimal point. I want to round up in the condional
formatting. Help?

Ad

B

#### Bernard Liengme

If I understand you correctly, use
=ROUND(B12/\$B\$10, 1) to round 0.46 to 0.50
This changes the STORED value while formatting only changes the DISPLAYED
value.
best wishes

S

#### SunshineinFt.Myers

Hi: my number is a % for example: 17.95 and its the color red. At .18 it is
supposed turn light green. I tried your formula and rounded it up to .20%.
its close but too much. Any ideas?

B

#### Bernard Liengme

We need to be careful here; you talk about 17.95% rounded to .20%. Do you
mean 20.00%
What is the EXACT setting for your Conditional Formatting?
best wishes

Ad

J

#### joeu2004

SunshineinFt.Myers said:
Hi: my number is a % for example: 17.95 and its the color red.
At .18 it is supposed turn light green. I tried your formula and
rounded it up to .20%. its close but too much. Any ideas?

See my response to your subsequent new thread.

PS: In the future, do not duplicate questions in multiple threads.

----- original posting -----

## Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.