miscalucation

[spence]

I just spent several hours trying to reconcile an Exel spreadsheet against
data from my accouting software only to discover that Excel is miscalculation
a simple formula. I have a column of dollar figures in cells formatted as
currency. If I use autosum, the formula reads:

=SUM(A1:A39)

and the total is $6447.60

But if I create the following formula:

=A1+A2+A3....etc to A39

the total is $6684.73, which is correct. (The difference is $237.13)

I have no hidden rows and have tried copying all the number into a new
spreadsheet. Can someone tell me what's going on here? Thanks.

[Dave Peterson]

Maybe some of those values in the cells aren't really numbers--they could be
text values that look like numbers.

If you put:
=counta(a1:a39) - count(a1:a39)
in a helper cell, do you get 0?

=counta() counts any old entry. =Count() counts real numbers.

You may be able to do this to correct the problem:
select an empty cell
Edit|copy
select A1:A39
edit|Paste special|check Add

Check your =sum() formula now.



spence wrote:
>
> I just spent several hours trying to reconcile an Exel spreadsheet against
> data from my accouting software only to discover that Excel is miscalculation
> a simple formula. I have a column of dollar figures in cells formatted as
> currency. If I use autosum, the formula reads:
>
> =SUM(A1:A39)
>
> and the total is $6447.60
>
> But if I create the following formula:
>
> =A1+A2+A3....etc to A39
>
> the total is $6684.73, which is correct. (The difference is $237.13)
>
> I have no hidden rows and have tried copying all the number into a new
> spreadsheet. Can someone tell me what's going on here? Thanks.

--

Dave Peterson