Round odd numbers for conditional formatting

[Traymond]

I have a sheet that has conditional formatting that looks at column D2
for a number and divides that by half. it then colors in a corresponding cell on a row in the sheet that matches that number. the problem i am having is with odd numbers like 23 it gives a result of 11.5 . all my cell numbers are all rounded to whole numbers. i need to round the 11.5 to 12 or 13 it does not matter which one... i just need it to round it up or down. I have tried everything i can think of but have had no luck. I attached my workbook so you can see how it works.

[StephenR]

How can one round 11.5 to 13?!

Please Login or Register  to view this content.

[JBeaucaire]

Use CEILING or FLOOR to push the number in the direction you want. Either:

=CEILING($D$2/2,1) (rounds up)
or
=FLOOR($D$2/2,1) (rounds down)

[Traymond]

Ceiling and Floor is just what i was looking for... thanks for the help

[JBeaucaire]

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED].



(Also, click on the "scales" icon across from the date on my post to leave Feedback, it is appreciated)