IF statements
 

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?

IF statements
 

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))

--
Regards
Roger Govier

"Dave in Ampthill" <Dave in wrote in
message ...
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?

IF statements
 

Hi Roger,

works perfectly so far. Many thanks for the prompt reply

Dave

"Roger Govier" wrote:

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))

--
Regards
Roger Govier

"Dave in Ampthill" <Dave in wrote in
message ...
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?

IF statements
 

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)

--
Rick (MVP - Excel)


"Dave in Ampthill" <Dave in wrote in
message ...
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?

IF statements
 

Very neat, Rick!

--
Regards
Roger Govier

"Rick Rothstein" wrote in message
...
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)

--
Rick (MVP - Excel)


"Dave in Ampthill" <Dave in wrote in
message ...
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?