Rounding within a range

  • Thread starter Thread starter husker
  • Start date Start date
H

husker

Is it possible to analyze and round a number based on the following
example:

If number is between *.25 and *.74 round to *.5
If number is between *.75 and *.99 round to next higher whole #.
If number is between *.01 and *.24 round to next lower whole #.

Thanks
 
=IF(A1-TRUNC(A1)<0.25,TRUNC(A1),IF(AND(A1-TRUNC(A1)<0.75,A1-TRUNC(A1)>=0.25),TRUNC(A1)+0.5,IF(A1-TRUNC(A1)>=0.75,TRUNC(A1)+1)))

I bet there's a less messy way, but this will work.
 
Back
Top