Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding
Hello,
I have a spreadsheet for calculating labor rate. Column A contains tenths of an hour (.1, .2, .3 etc...) Column B has a simple formula (=A1*32.39), and is filled down. Column B is formatted to currency, and 2 decimal places (which I need to maintain). When I look at, for example .3, the result is $9.72. Then I look at .6, and the result is $19.43. If you multiply $9.72 by 2, the result is $19.44, 1 penny off from the spreadsheet. I have tried some rounding functions, but the results were still off like this. Any suggestions? Thanks, -Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding
I haven't fully tested it out, but in this particular situation you could use
the "Ceiling" function (rounds up to a certain significance - in this case, rounds up to the nearest cent). =CEILING(A7*32.39,0.01) It's worth a try... if that doesn't work for you, you may just have to chalk it up to "dirty math with decimals" :) "Steve" wrote: Hello, I have a spreadsheet for calculating labor rate. Column A contains tenths of an hour (.1, .2, .3 etc...) Column B has a simple formula (=A1*32.39), and is filled down. Column B is formatted to currency, and 2 decimal places (which I need to maintain). When I look at, for example .3, the result is $9.72. Then I look at .6, and the result is $19.43. If you multiply $9.72 by 2, the result is $19.44, 1 penny off from the spreadsheet. I have tried some rounding functions, but the results were still off like this. Any suggestions? Thanks, -Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding
It was looking good, but I did find inconsistencies.
..5 = 16.20, but if you double that, the result is 32.40 - 1 penny higher than 32.39. ..6 = 19.44, but if you double that, the result is 38.88 - whereas 1.2 (double the .6) is 38.87 - 1 penny less. It keeps going on like that... Thanks for the attempt Lisa. "Lisa" wrote in message ... I haven't fully tested it out, but in this particular situation you could use the "Ceiling" function (rounds up to a certain significance - in this case, rounds up to the nearest cent). =CEILING(A7*32.39,0.01) It's worth a try... if that doesn't work for you, you may just have to chalk it up to "dirty math with decimals" :) "Steve" wrote: Hello, I have a spreadsheet for calculating labor rate. Column A contains tenths of an hour (.1, .2, .3 etc...) Column B has a simple formula (=A1*32.39), and is filled down. Column B is formatted to currency, and 2 decimal places (which I need to maintain). When I look at, for example .3, the result is $9.72. Then I look at .6, and the result is $19.43. If you multiply $9.72 by 2, the result is $19.44, 1 penny off from the spreadsheet. I have tried some rounding functions, but the results were still off like this. Any suggestions? Thanks, -Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding
Give evryone a penny rise to 32.40 otherwise I don't believe that it will
ever work out. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Steve" wrote in message ink.net... Hello, I have a spreadsheet for calculating labor rate. Column A contains tenths of an hour (.1, .2, .3 etc...) Column B has a simple formula (=A1*32.39), and is filled down. Column B is formatted to currency, and 2 decimal places (which I need to maintain). When I look at, for example .3, the result is $9.72. Then I look at .6, and the result is $19.43. If you multiply $9.72 by 2, the result is $19.44, 1 penny off from the spreadsheet. I have tried some rounding functions, but the results were still off like this. Any suggestions? Thanks, -Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rounding help | Excel Discussion (Misc queries) | |||
Rounding .01 to 1 instead of 0 | Excel Discussion (Misc queries) | |||
Rounding Down | Excel Discussion (Misc queries) | |||
Rounding up help | Excel Discussion (Misc queries) | |||
Rounding | Excel Discussion (Misc queries) |