Rounding Problem

[MsBindy]

I'm having trouble figuring out why my formula is not rounding as intended.

I am trying to round the cost rate to 2 places;  then multiply the rounded cost rate times the hours.

Seems pretty simple,  but I keep ending up one penny off.

[MarqueeCrew]

You're rounding twice here.

Why not just round in the CP Total DTS formula and not round in the CP Wage Rate Paid.

Round(67.965 * 3.5,.01) should equal 237.88

Cheers,
Mark

[MsBindy]

In my final report I need to display the rate (to 2 decimal places),  the hours, and the product of the 2 (to 2 decimal places)

My 3 columns would be:

Rate:  67.97

Hours  3.5

DTS:   237.90

Oddly,  I didn't even know it wasn't working until I started to turn this workflow into In-DB and started to see pennies all over the place!

[MarqueeCrew]

Ok...

How about this workflow to solve it?

Cheers,

Mark

[MsBindy]

Then I'm 2 cents off

I need to show all 3 fields and they have to cross-foot.

3.5  * 67.97(rounded)  = 237.90(rounded)

[DanS]

The attached should work. 

You were rounding to the nearest hundredth on the Cost (which is 0), so changing to the below would return the 237.90

Round([CP Wage Rate Paid] * [CP Total Hours], .1)

Let me know if this is what you were hoping for, I could be missing something!

[MsBindy]

Nope, that's not it;  the second line you added only goes to one decimal place;

so it worked for the first line, but not the second.

[MsBindy]

So now I have 2 examples to better illustrate.

In one,  I do some calculations to come up with a result of 289.895;  I then round it and get 289.89

In the second,  I do no calculations, but just simply put in the number 289.895;  apply the round formula and get 289.90

I can't see why I'd get 2 different results!

I know it is only a penny, but I need precision.

[MsBindy · Accepted solution]

This is crazy,  but I finally got something to work.

round(round(round([Cost Rate],.01)*[Hours],.001),.01)

I round the cost rate to 2 decimal places,  do the multiplication and round to 3 places,  take that number and round back to 2 places

[david_fetters]

Wow, that's actually a neat little problem you've found.  It seems like it is not handling the multiplication and rounding in the same formula field very well.

A quick solution is to have an intermediate field that is a float/double with the multiplication: e.g. [New_Temp_Field] = [CP Wage Rate Paid]*[CP Total Hours]

Then create another formula field to round that: [CP Total DTS] = round([New_Temp_Field], .01).  This worked for me.  It's not the most elegant of solutions, and the problem you discovered certainly requires some more investigation, but this should get you running for the moment while we dig into this a bit more.