Rounding a calculated field

[bigalpha]

I have a field that sums pounds. I need to convert this to tons (rounded to the nearest 3 decimals), then multiplied by $67.50. This will calculate a fee payment.

This is what I have now:

=Round(Sum([Hazardous_Waste]/2000*67.5),3)

The total pounds is 2675.
After dividing by 2000, Access generates a number of 1.3375. It rounds 1.3375 to 1.337 which generates an incorrect final total. Not sure how to alter this to round properly.

Thanks.

 

[boblarson]

See if this helps

http://allenbrowne.com/round.html

 

[MarkK]

What happens if you do this . . .

=Round(Sum([Hazardous_Waste])/2000*67.5,3)

. . . ?
What I would likely do though is add a textbox, say tbSumOnly, and do this . . .

=Sum([YourData])

. . . and then do this . . .

=Round([tbSumOnly]/2000*67.5, 3)

. . . to keep the whole process really clear.
hth
ps. and you can hide one of those textboxes too, but in design it's clear what you are doing.

 

[bigalpha]

What happens if you do this . . .

=Round(Sum([Hazardous_Waste])/2000*67.5,3)

. . . ?

I get 90.281.

What I would likely do though is add a textbox, say tbSumOnly, and do this . . .

=Sum([YourData])

. . . and then do this . . .

=Round([tbSumOnly]/2000*67.5, 3)

. . . to keep the whole process really clear.
hth
ps. and you can hide one of those textboxes too, but in design it's clear what you are doing.

I still get 90.281.

The result I need is 90.32.

 

[bigalpha]

See if this helps

http://allenbrowne.com/round.html

This is where I got the Round function from. I'm not sure how to use the multiply then divide trick in my expression to get my field to round, though. Math is not my strong subject.

 

[MarkK]

2675 / 2000 * 67.5 =  90.28125

round that to 3 places and you get

90.281

If you need to get 90.32 then you need two decimal places and a different mathematical expression than the one you posted about.

 

[bigalpha]

2675 / 2000 * 67.5 =  90.28125

round that to 3 places and you get

90.281

If you need to get 90.32 then you need two decimal places and a different mathematical expression than the one you posted about.

I need the tons rounded to the nearest 3 decimal places. After that rounding takes place, then I multiply by 67.5.

Edit: Sorry, I thought I included in my initial post that while the tons round to 3 decimal places - the end result will be a cost, so it's 2 decimal places.

 

[MarkK]

I need the tons rounded to the nearest 3 decimal places.

Round(2675/2000, 3)

After that rounding takes place, then I multiply by 67.5.

Round(2675/2000, 3) * 67.5

 

[boblarson]

Did you try Allen Browne's method I posted?

 

[bigalpha]

boblarson - Not sure which one of those is most applicable to what I'm trying to accomplish. I assume it's the Round function.

At any rate, the solution that lagbolt posted works great. I appreciate the help.