Round to .05 or .09
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 |
Round to .05 or .09
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. -- Regards, Eddie http://www.HelpExcel.com "Bruce" wrote: 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 |
Round to .05 or .09
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)) -- Regards, Eddie http://www.HelpExcel.com "Bruce" wrote: 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 |
Round to .05 or .09
Bruce wrote...
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. |
Round to .05 or .09
Bruce wrote...
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. |
All times are GMT +1. The time now is 10:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com