B
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
Eg 1
5.01 to 4.99
Eg 2
4.44 to 4.45
Regards,
Bruce
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
H
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.
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.
H
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.
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.
H
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))
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))
H
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))
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))
H
Harlan Grove
Bruce said: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.
H
Harlan Grove
Bruce said: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.
H
Harlan Grove
Bruce said: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.
H
Harlan Grove
Bruce said: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.