I have a calculation of an average of 4 & 7 nos. I want to round down any
thing between .1 & .3 e.g. 3.3 = 3.0

roundup/down to .5 if between .4 & .6 e.g 3.4 = 3.5 & 3.6 =3.5

and round up between .7 and .9 eg 3.7 to 4.0
and if a whole no leave alone e.g 3.0 = 3.0

--
Stuart

<Stuart Carnachan wrote in message
...
I have a calculation of an average of 4 & 7 nos. I want to round down any
thing between .1 & .3 e.g. 3.3 = 3.0

roundup/down to .5 if between .4 & .6 e.g 3.4 = 3.5 & 3.6 =3.5

and round up between .7 and .9 eg 3.7 to 4.0
and if a whole no leave alone e.g 3.0 = 3.0

=IF(AND(MOD(A1,1)0.35,MOD(A1,1)<0.65),MROUND(A1,0 .5),INT(A1)) , adjusting
your boundaries as it suits you between 0.3 & 0.4, and between 0.6 and 0.7.
Remember that MROUND needs the Analysis ToolPak add-in.
--
David Biddulph

"David Biddulph" wrote in message
...
<Stuart Carnachan wrote in message
...
I have a calculation of an average of 4 & 7 nos. I want to round down any
thing between .1 & .3 e.g. 3.3 = 3.0

roundup/down to .5 if between .4 & .6 e.g 3.4 = 3.5 & 3.6 =3.5

and round up between .7 and .9 eg 3.7 to 4.0
and if a whole no leave alone e.g 3.0 = 3.0

=IF(AND(MOD(A1,1)0.35,MOD(A1,1)<0.65),MROUND(A1,0 .5),INT(A1)) , adjusting
your boundaries as it suits you between 0.3 & 0.4, and between 0.6 and
0.7.
Remember that MROUND needs the Analysis ToolPak add-in.


Correction:
=IF(AND(MOD(A1,1)0.35,MOD(A1,1)<0.65),MROUND(A1,0 .5),ROUND(A1,0))
--
David Biddulph

=INT(A1)+IF(MOD(A1,1)<0.4,0,IF(MOD(A1,1)<0.7,0.5,1 ))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

<Stuart Carnachan wrote in message
...
I have a calculation of an average of 4 & 7 nos. I want to round down any
thing between .1 & .3 e.g. 3.3 = 3.0

roundup/down to .5 if between .4 & .6 e.g 3.4 = 3.5 & 3.6 =3.5

and round up between .7 and .9 eg 3.7 to 4.0
and if a whole no leave alone e.g 3.0 = 3.0

--
Stuart