If I am averaging a set of data and only want the
answer to have the decimal place of either .1, .5, or .9 how can I round off
the answer (either up or down) to the nearest of those required decimal
places?
Thanks for any help!

To do this you can use MRound(A2,0.5), but you would need the analysis
toolpack installed.

"John Mac" wrote:

If I am averaging a set of data and only want the
answer to have the decimal place of either .1, .5, or .9 how can I round off
the answer (either up or down) to the nearest of those required decimal
places?
Thanks for any help!

Me again, here you go
=IF(B1=0.8,0.9,IF(B10.3,0.5,0.1))
are you needing that to go with your other formula?
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"John Mac" wrote:

If I am averaging a set of data and only want the
answer to have the decimal place of either .1, .5, or .9 how can I round off
the answer (either up or down) to the nearest of those required decimal
places?
Thanks for any help!

John
Thanks for your response - I did not make it clear what I required. I still
need the units to have the correct number eg. the average of 5.1 + 5.5 + 5.9
+ 5.1 = 5.4 but I need the answers to be rounded off to the nearest .1,.5 or
..9 (in this example 5.5)

"John Bundy" wrote:

Me again, here you go
=IF(B1=0.8,0.9,IF(B10.3,0.5,0.1))
are you needing that to go with your other formula?
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"John Mac" wrote:

If I am averaging a set of data and only want the
answer to have the decimal place of either .1, .5, or .9 how can I round off
the answer (either up or down) to the nearest of those required decimal
places?
Thanks for any help!

Using this technique how do I round everything either up or down to the
nearest .1,.5 or.9?

"~L" wrote:

To do this you can use MRound(A2,0.5), but you would need the analysis
toolpack installed.

"John Mac" wrote:

If I am averaging a set of data and only want the
answer to have the decimal place of either .1, .5, or .9 how can I round off
the answer (either up or down) to the nearest of those required decimal
places?
Thanks for any help!

Sorry, I didn't read that carefully enough. To do what you are asking, I
used this formula:

=IF(OR(MOD(D2,1)=0.1,MOD(D2,1)=0.5,MOD(D2,1)=0.9), D2,IF(MOD(D2,1)=0.3,IF(MOD(D2,1)=0.7,--(INT(D2)&".9"),--(INT(D2)&".5")),--(INT(D2)&".1")))

"John Mac" wrote:

Using this technique how do I round everything either up or down to the
nearest .1,.5 or.9?

"~L" wrote:

To do this you can use MRound(A2,0.5), but you would need the analysis
toolpack installed.

"John Mac" wrote:

If I am averaging a set of data and only want the
answer to have the decimal place of either .1, .5, or .9 how can I round off
the answer (either up or down) to the nearest of those required decimal
places?
Thanks for any help!

Hi John,

If your average is in A1, then take
=INT(A1)+CHOOSE((MOD(A1,1)<0.3)+(MOD(A1,1)<0.7)+1, 0.9,0.5,0.1)

HTH,
Bernd