[worrier]

I have been doing my business accounting in excel. I plug in my formula and sums are calculated for me. I keep an account summary for my client and I keep a separate summary for myself. In my summary, excel is giving me a false result. I have added the numbers by hand and compared it to the client summary and the result is off by 2 cents. This is driving me nuts because I have checked and rechecked my formula. I have even gone to the extreme of opening a new workbook and re-entering all my data and it is still off. I cannot figure it out because if excel wasn't functioning properly, why would my client summary be correct and not my personal summary?? I know it is only 2 cents but I need to figure this out!!

Thanks for any help!

[ntlgnce]

This is just a simple problem. to error is human!! re check all your numbers to be shure you put them in right.

[worrier]

Thanks for the suggestion ntlgnce, but I have put in every number correctly, I have put in the correct formula...I have triple checked. It isn't my error, it is excel. Here is an EXAMPLE of my problem.

A
1 $2.98
2 $3.25
3 $4.10
4 $6.33
5 $3.76
6 =sum(a1:a5)

The answer should be $20.42 but it is showing up as a couple cents less ($20.40) By the way, it is doing the same thing in other column values also.


Anyone else have any ideas??

[ntlgnce]

To error is Ntlgnce... have you tried the format cells? there is a way to assign values to each cell perhaps it is in there (I don't know that much about it) but if you put it on like # you can change the options like 4.00 23.23.4 something there possably???

[EAFiedler]

Hi worrier

I would check the number formatting to make sure you are not carrying more than two decimal places.

[Anne Troy]

$2.9760
$3.2460
$4.0960
$6.3250
$3.7611
$20.4041

If you type these numbers into Excel, and then format it all to 2 decimals, you'll get the same results you're getting right now.

[worrier]

Thank you all!
I checked my formatting and it is set to 2 decimals before the amounts are put in.

Dreamboat - you made me think of something and perhaps if I had given you all more information you would have been able to figure this out.
The dollar amounts that I listed are actually a result of another formula. I have 4 columns with number values and 2 of them have formulas entered into them. For example, I have a column where I list the character count from my word document - that is a manual entry. In the next column I have a formula entered to calculate the number of lines, which I preformat to 1 decimal place. The next column is a manual entry of the per line charge. And the last column is the dollar amount - a formula entry. So here is what it looks like:

4441 =sum(a1/65) $.10 =sum(b1*c1)

And then at the bottom of the last column I have the formula that I mentioned before(=sum(d1:d?)). I was just thinking that maybe my problem could be in the second column. Do you think that my formula in the last column could be reading the unrounded number in the second column even though I have it formatted to just 1 decimal place? If that is my problem, how can I correct it aside from manually calculating and entering the line count?

I know this post was long winded...I hope I didn't confuse anyone!

By the way, in my client summary the line count is entered manually (result taken from my personal summary) and maybe that's why client summary is coming out correct?

Thank you!

[Edited by worrier on 04-06-2001 at 08:04 AM]

[Anne Troy]

Worrier:

Format does not mean diddly. It's calculating the number that you see when you've got a gazillion decimal points...

Format does not mean diddly. I can put 4/6/2001 in a cell and format it to show just 4/6. Then, when I take 4/7/2001 and put it into another cell and subtract 4/6, I get 1 (day). I can change the original 4/6/2001 by going up into the formula bar on that cell and change it to 4/6/2000. Now it is still showing 4/6 in the formula bar, but my number will have changed from 1 to something like 366 (days).

Hope this makes sense for you. Format is just a DISPLAY, not a value. Same in Access too.

[worrier]

I understand what you are saying. I guess the only way I can resolve this is to calculate the line count (second column) manually and enter it in as a set number so further functions have the correct result? I wish there were another way to do this....

Thanks for setting me straight!

-worrier

[Anne Troy]

Nope. Sorry. Wasn't thinking.

Add the bolded parts to your current formulas:

=ROUND(SUM(A1:A10),2)

=ROUND(B5*B9,2)

and this will round the VALUE (not the display!) to two decimals.

[worrier]

Your suggestion worked!!! Wow, you learn something new every day...thank you

In the second column: =round(sum(a1/65),1)
In the fourth column: =round(sum(b1*c1),2)

[Edited by worrier on 04-06-2001 at 11:56 AM]