IF statements

[Dave in Ampthill]

I wish to create a cell that gives a rounded up/rounded down figure i.e.

Assume figure of 20.38

The number before the decimal point will be variable.

The number after the decimal point will be subject to the following
conditions:-

If < .124 then round down to next whole 0.5 (if 20.38 then figure to be 20.0)
If > .124 then round up to next whole 0.5 (if 20.38 then figure to be 20.5)
If < .624 then round down to next whole 0.5 (if 20.45 then figure to be 20.5)
If > .624 then round up to next whole 0.5 (if 20.72 then figure to be 21.0)

How do I write the formula please?

 

[Roger Govier]

Hi Dave

maybe
=IF(OR(MOD(A1,1)>0.624,AND(MOD(A1,1)>0.124,MOD(A1,1)<=0.5))
,CEILING(A1,0.5),FLOOR(A1,0.5))

 

[Dave in Ampthill]

Hi Roger,

works perfectly so far. Many thanks for the prompt reply

Dave

 

[Rick Rothstein]

If you return to this thread, another possibility (if you have the Analysis
ToolPak Add-In installed) is this...

=MROUND(MROUND(A1,0.25),0.5)

 

[Roger Govier]

Very neat, Rick!

--
Regards
Roger Govier

If you return to this thread, another possibility (if you have the
Analysis ToolPak Add-In installed) is this...

=MROUND(MROUND(A1,0.25),0.5)