Round to .05 or .09

[Bruce]

How can I round prices to the nearest 0.05 or 0.09?

Eg 1
5.01 to 4.99
Eg 2
4.44 to 4.45

Regards,

Bruce

 

[HelpExcel.com]

Bruce,

You can use the following formula:

=IF(MIN(ABS(RIGHT(A3*100,1)-5),ABS(RIGHT(A3*100,1)+10-5))<MIN(ABS(RIGHT(A3*100,1)-9),ABS(RIGHT(A3*100,1)+10-9)),(INT(A3*10)+0.5)/10,(INT(A3*10)+0.9)/10)

Replace cell A3 with the cell containing the 2 DIGIT number. This also
loses precision as the numbers 2 & 7 are equidistant from the 5 and 9.

 

[HelpExcel.com]

Bruce,

Major adjustments

=IF(MIN(ABS(RIGHT(A3*100,1)-5),ABS(RIGHT(A3*100,1)+10-5))<MIN(ABS(RIGHT(A3*100,1)-9),ABS(RIGHT(A3*100,1)+10-9)),IF(ABS(RIGHT(A3*100,1)-5)<ABS(RIGHT(A3*100,1)+10-5),(INT(A3*10)+0.5)/10,(INT(A3*10-1)+0.5)/10),IF(ABS(RIGHT(A3*100,1)-9)<ABS(RIGHT(A3*100,1)+10-9),(INT(A3*10)+0.9)/10,(INT(A3*10-1)+0.9)/10))

 

[Harlan Grove]

How can I round prices to the nearest 0.05 or 0.09?

Eg 1
5.01 to 4.99
Eg 2
4.44 to 4.45

Meaning the last digit would always be either a 9 or a 5?

to 0.09: =ROUND(x,1)-0.01

to 0.05: =ROUND($A1,1)-0.05

Note: using the first, 5.04 rounds down to 4.99; using the second, 4.

 

[Harlan Grove]

How can I round prices to the nearest 0.05 or 0.09?

Eg 1
5.01 to 4.99
Eg 2
4.44 to 4.45

to 0.09: =ROUND($A10+0.011,1)-0.01

to 0.05: =ROUND($A10+0.051,1)-0.05

or

to 0.09: =ROUND($A10+0.009,1)-0.01

to 0.05: =ROUND($A10+0.049,1)-0.05

depending on whether you want to round, e.g., 5.04 up to 5.09 or down
to 4.99.