Formula to round based on thousandths place

[ProHealth, Inc.]

I'm trying to write a formula to round a number with the following
conditions:

1. If the number in the thousandths place is a 0, 4, 5, 6, 7, 8 or 9
round to the hundredths place
2. If the number in the thousandths place is a 1, 2, 3 or 4 add 0.01
and round to the hundredths place

Here is the formula I have so far (there's probably a cleaner/shorter
way of doing this):

=IF(AND(MID(Q23,FIND(".",Q23)+3,1) >=5,(MID(Q23,FIND(".",Q23)+3,1)
<=9)), ROUND(Q23,2), IF(MID(Q23,FIND(".",Q23)+3,1)=0, ROUND(Q23,2),
CEILING(Q23,0.01)))

I'm having trouble getting it to round correctly if the number in the
thousandths place is a 0.

Examples:
10.9607000 is being rounded to 10.97 instead of 10.96
19.8401000 is being rounded to 19.85 instead of 19.84

 

[Bill Kuunders]

There are probably different solutions,
but going with your formula, wouldn't you want to add 0.001 in stead of 0.01
at the end?

 

[Pete_UK]

You have 4 in both lists. However, this formula seems to do what you
intended:

=ROUND(A1+IF(OR((A1*100-INT(A1*100))>=0.5,(A1*100-INT(A1*100))<0.1),
0,0.01),2)

Here's some test results:

10.96070 10.96
19.84010 19.84
10.00000 10.00
10.00100 10.00
10.00200 10.01
10.00300 10.01
10.00400 10.01
10.00500 10.01
10.00600 10.01
10.00700 10.01
10.00800 10.01
10.00900 10.01
10.01000 10.01

Is this what you want?

Hope this helps.

Pete

 

[Bernard Liengme]

How about
=IF(OR(MOD(INT(A1*1000),10)={1,2,3,4}),ROUNDUP(A1,2),ROUNDDOWN(A1,2))
best wishes

 

[ProHealth, Inc.]

Thank you all for your answers; sorry I haven't been able to get back
to you until now.

I'll try out the solutions given and let you know what happens.

Thanks again!!