Rounding issue

[daanderson]

Hi, I have been struggling with a problem and hoping someone can help me. I have an extensive workbook with numerous formulas, pivot tables, etc. One thing feeds off another and so on. I have had a number of issues with it and have always been able to get help here, so here goes...

My current problem is rounding. A lot of my calculations take an hour amount times a rate. This information is "sucked out" as values and forwarded to another agency. When they pull this information into their database, we are coming up with 1 cent and 2 cent variances - which is a show stopper and needs to be redone. I have tried changing to "precision as displayed", turning "precision as displayed" off (seems to get a little more accurate actually - but not enough).

Is there anything I can include in a formula to make this stop? To stop at the 2 decimal places and do not include anything past that?

I am on my fifth submission of this report for pennies!

Thanks a bunch for any help!

 

[Andrew Poulsom]

Have you tried using the ROUND function in your formulas, eg?

=ROUND(YourFormula,2)

 

[Gerald Higgins]

Have you tried rounding formulas ?

Your options are basically
Roundup - 3.2 becomes 4
Rounddown - 3.8 becomes 3
Round - 3.2 becomes 3 and 3.8 becomes 4.

You need to decide yourself which one suits your application, but once you've decided, this should help you.

 

[Gerald Higgins]

Sorry I was oversimplifying.

As Andrew Poulsom alluded to, you have more detailed options.

For example, round(1.234,0) will return 1.
But round (1.234,2) will return 1.23 and so on.

 

[daanderson]

Thanks! This should do it!

 

[Scott Huish]

Also note that if you use ROUNDUP and ROUNDDOWN, negative numbers will do the opposite. There are ways around this, it just depends on what you need.

With =ROUNDUP(A1,0) for example:
3.8 becomes 4
-3.8 becomes -4