Articles

my excel isnt calculating correctly.

October 15, 2011 at 11:31:22
Specs: Windows 7

my excel isnt calculating correctly. i know it sounds odd but its true. im doing some simple spreadsheets too. one is a payroll sheet, the other one is account receivables. to make this short, i have an open bill for 13.33, i got paid for it and i jotted down the info in the next cell, which was 13.33. but, excel tells me that im short a penny. i dont know? even my wife whos an accountant and uses excel everyday is clueless. please help

See More: my excel isnt calculating correctly.

Report •


#1
October 15, 2011 at 12:17:34

How was the original 13.33 calculated?

If it was the result of a formula and there is rounding that is caused by only displaying 2 decimal places, that could be your issue.

Keep in mind that even if Excel only display X number of decimal places, the actual values are still stored internally.

Try this:

Format A1:A3 as a number with 2 decimal places.

In A1 enter 1.652
In A2 enter 1.654

They will both display 1.65.

SUM what you see with your brain and you'll get 3.30, right?

Now, in A3 enter =SUM(A1:A2) and you'll get 3.31.

Is that off by a penny? No, since 1.652 + 1.654 = 3.306.

Since all three cells are formatted to display 2 decimal places, A1 and A2 round down to 1.65 but A3 rounds up to display 3.31.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 15, 2011 at 12:27:54

i know when i push the decimal to the 3rd decimal it shows 13.325 and im putting in 13.330. which would give me -.05. i do understand that. but isnt there a way to calculate from the second decimal, not the 3rd 4th or 5th. it the same as in my payroll sheet. i dont calculate cents everything is round up to the dollar. but the final total is alway short one dollar. so my question is how do i get around that

Report •

#3
October 15, 2011 at 13:18:53

So if you know about the "displayed vs. actual value" issue, then to say "my excel isn't calculating correctly. i know it sounds odd but its true" isn't correct, is it?

Excel is calculating 100% correctly, you just don't want it to.

There are 3 Excel functions you can use to force Excel to calculate based on the number of decimal places you want it to use.

ROUND(number, num_digits)

This will use the standard rounding rules: 4 or below is ignored, 5 or above is raised to the next highest number.

=ROUND(13.325, 2) = 13.33
=ROUND(13.324, 2) = 13.32

ROUNDUP(number, num_digits)

This will round the number up regardless of the value of the digit that you are checking.

=ROUNDUP(13.325, 2) = 13.33
=ROUNDUP(13.324, 2) = 13.33

ROUNDOWN(number, num_digits)

This will round the number down regardless of the value of the digit that you are checking.

=ROUNDDOWN(13.325, 2) = 13.32
=ROUNDDOWN(13.324, 2) = 13.32

If you SUM the results of these functions, you will get a value based on the results, not based on the original numbers.

e.g.

=SUM(13.324, 13.324) = 26.648 which when displayed with 2 digits will show 26.65.

=SUM(ROUND(13.324, 2),ROUND(13.324, 2)) = 26.64 since the .4's were stripped off before the values were summed.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
October 16, 2011 at 07:14:36

For financial data any operation using certain operations must be rounded or the errors will propagate. It depends upon the accounting rules. If you don't then there will be problems in auditing.

Report •

#5
October 16, 2011 at 07:53:16

re: "any operation using certain operations"

Huh? ;-)

As long as we're on the subject of the movie "Office Space", check out the story of Michael Largent, who took the "stealing pennies" from a bank in a slightly different direction.

First story here:

http://www.cracked.com/article_1737...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
October 18, 2011 at 11:20:05

thanks guys. i totally forgot about the rounding formulas. good looking out everyone. deepely appreciated

Report •


Ask Question