Rounding in IF statements

**Aditi208** · 08-08-2012, 02:05 PM

How would I write a formula that includes the following three stipulations?

If C3 < 0.5: round to the nearest 0.1
If C3 > or = 0.5 to < or = 5.0: Round to the nearest 0.5
If C3 > 5: round to the nearest 1

**daddylonglegs** · 08-08-2012, 02:09 PM

Try this formula

=IF(C3<0.5,ROUND(C3,1),IF(C3>5,ROUND(C3,0),ROUND(C3*2,0)/2))

**Aditi208** · 08-08-2012, 02:40 PM

that formula works great! however my only problem is that it doesn't keep the decimal places if it rounds to a whole number for the second stipulation. for example it rounds 2.183 to 2 instead of 2.0. is there any way to fix that?

**ben_hensel** · 08-08-2012, 04:02 PM

It does round 2.183 to 2.0 instead of "2"; you want to fix something that isn't broken.

But you haven't set the cells to display that. (I hazard to guess you've left it the cell format as "general", which omits trailing zeros).

Right click => cell format=> number: 1 decimal place