How to remove automated rounding?

[Maki]

Hi

An invoice template I downloaded seems to do the rounding of sum to nearest
5 or 0; ex. Sum of $660.48 and $4816.38 shows as $5476.85. I don't need this
type of rounding as I'm not dealing in cash. How can I rectify this? Format
Cell>Number>Category is Custom and Type is
_($*#,##0.00_);_($*#,##0.00_);_($*"-"??);_(@_)
I don't understand the syntax either.

Thanks for help.

 

[T. Valko]

That format is not causing the rounding.

It's probably being rounded with a formula, or, it could be being done with
VBA code.

 

[Maki]

It is definitely not formula, Biff, as I do not see =ROUND** or =MROUND.
Hmmm, how can I see what VBA codes are at work?

 

[Maki]

Sorry, I haven't indicated what version I'm using.
It's Excel 2007 and that's part of the confusion. I can't navigate the way
I could in the previous versions of Excel.

 

[T. Valko]

Where did you get the template? If it's a commercial product all of the
"important stuff" is probably hidden and protected from view.

Do you see *any* formula in those cells?

 

[Maki]

It was one of the invoices at Office's official site.
http://office.microsoft.com/en-au/templates/TC011060141033.aspx?CategoryID=CT101172551033

I subsequently deleted cells from GST downwards and made SUBTOTAL into TOTAL
as I didn't need GST calculation afterall.

All I see in the SUBTOTAL cell is =SUM(D1830) and no formula for rounding...

Clicked on View tab>Macros>View Macros but nothing shows.

 

[T. Valko]

seems to do the rounding of sum to nearest 5 or 0

Ok, when you mentioned the above, to me, that seems like an intentional
pattern. However, if there is no ROUND function in the formula and no macros
then there is no intentional rounding taking place. It may be just a
coincidence that the results seem to be ending up as 5 or 0.

You may need to round upstream calculations to get the correct results you
expect. FORMATTING only changes the *appearance* of the value in a cell. For
example, a cell may display as 2.00 but its true underlying value might
actually be 2.0035419. When you use this cell in calculations Excel uses the
true underlying value 2.0035419, not 2.00.

See this:

http://mcgimpsey.com/excel/pennyoff.html