Advanced Rounding

[amir9s]

Hello everyone,
I'm having a difficult time figuring this problem out.
I'm trying to round up and down numbers depending on their decimal value.

Basically

• if decimal is equal/over .65, then round up to the next whole number
• if decimal is between 0.25 and .65 then enter 0.5
• and finally if decimal is smaller than 0.25 then round it down to the previous whole number.

as an example:
1.24 returns 1.0
1.25 returns 1.5
3.8 returns 4.0
2.65 returns 3.0
2.64 returns 2.5
and so on...

Thank you in advance

 

[alfhasian]

It's a bit messy, but a nested IF statement could work for this. Assuming the value is in A1 and the rounded value is in B1:

=IF(A1-INT(A1)<0.25,ROUNDDOWN(A1,0),IF(AND(A1-INT(A1)>=0.25,A1-INT(A1)<0.65),INT(A1)+0.5,ROUNDUP(A1,0)))

 

[alfhasian]

B1 is irrelevant for the above example, but I meant to say that I had typed the above equation into cell B1 to verify that it would work.

 

[Eric W]

A bit shorter:

=INT(A1)+IF(MOD(A1,1)<0.25,0,IF(MOD(A1,1)>=0.65,1,0.5))

 

[amir9s]

Thank you both very much!

 

[Eric W]

Glad we could help.

 

[SpillerBD]

to split hairs, how about

=IF(AND(MOD(A1,1)>=0.25,MOD(A1,1)<0.65),MROUND(A1,0.5),ROUND(A1,0))

 

[alfhasian]

A bit shorter:

=INT(A1)+IF(MOD(A1,1)<0.25,0,IF(MOD(A1,1)>=0.65,1,0.5))

Wow, MUCH shorter!

 

[shg]

Or

=INT(A1)+LOOKUP(MOD(A1,1),{0,25,65}%,{0,0.5,1})

 

[Eric W]

shg, I actually considered that version, but it generates an inaccurate result when the decimal is exactly .65. I got it to work by using:

=INT(A1)+LOOKUP(MOD(A1,1),{0,0.25,0.6499},{0,0.5,1})

but I didn't know how many 9's would be required, so I stuck with the original version.