Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
round up | Excel Worksheet Functions | |||
round up | Excel Discussion (Misc queries) | |||
Round up or down | Excel Discussion (Misc queries) | |||
Round up to 100? | Excel Worksheet Functions | |||
How do I ROUND() round off decimals of a column dataset? | Excel Worksheet Functions |