I work with currency in Excel and I need a way for Excel to round to the nearest hundredth without excluding decimal places. What I mean is, when you take the equation $123.50*0.8717 the answer is $107.65495. When you use the formula ROUND($123.50*0.8717,2) the answer is $107.65.
However, it should start at the last number and work its way in. The correct answer is $107.66. A lot of numbers I work with end up being like this causing penny differences. It's not a big deal when it's just one or two numbers, but think of Office Space, where it's thousands of numbers every day. The amounts add up. Is there a simple way to get Excel to round correctly to the hundredths place?
I currently get around this issue by doing:
=ROUND(ROUND(ROUND($123.50*0.8717,4),3),2)
But not every number I work with only goes out to five decimal places, some have more.
However, it should start at the last number and work its way in. The correct answer is $107.66. A lot of numbers I work with end up being like this causing penny differences. It's not a big deal when it's just one or two numbers, but think of Office Space, where it's thousands of numbers every day. The amounts add up. Is there a simple way to get Excel to round correctly to the hundredths place?
I currently get around this issue by doing:
=ROUND(ROUND(ROUND($123.50*0.8717,4),3),2)
But not every number I work with only goes out to five decimal places, some have more.
