Round function

Hi Everyone,

If you add 205.95 + 12.36 + 16.48 by calculator it =$ 234.79. But on excel it is $234.78?. The problem I have is on invoices I am doing for accounting on excel are showing some off by 1 cent and it does not balance. Thought I would ask you if know a formula

thank you,
Sassy

I just did both on my calc and in excel. fro my fomula i used =sum(cell:cell). for instance on my spreadsheet i used cells: A1, A2, A3 with the total going in cell A4. in cell A4 i put in the formula =sum(A1:A3). hope this helps

Sorry let me clarify more. Typing the numbers without fourmlas, adds up correctly in excel . But using the following formula causes total amount to be off by 1 cent.

E9=$205.95 + GST 6% + PST 8%

205.95 + =SUM(E9*6%)+ =SUM(E9*8%)

Total above came to 205.95 + 12.36 + 16.48 =$234.78 when forumla was used, making it off by 1 cent.

Although excel rounds numbers for you, it still recognizes them as their true value. For example, 1.005 might show up as 1.01, but 1.01+1.01 (which is really 1.005+1.005) will equal 2.01.

If you want to avoid this, nest your sum function within a round function.

For example: =205.95+ROUND(E9*6%,2)+ROUND(E9*8%,2)

Let me know if that works

BigBas you just made my day !!! Thank you SO much for helping me out with that. I have over 300 invoices to code and you just made life easier on me.

Enjoy your day !

I'm glad I could help. That little quirk used to annoy me to no end, especially when try to use a function that looks for an exact match.