Excel 2002 > Nbrs to not add up correctly

[Wayne G. Dengel]

Is it common for Excel 2002 to not add correctly, i.e. be off by a factor
of 1?

I have a file - 30-40 numbers, $s w/o cents - plain dollars. I found
that I was off by $1.oo this afternoon - mine compared to our accountant
where her tape showed $1 greater (hers was correctly higher by $1.).

I then added a new column with the units nbrs from the original big dollars
column only: $2,003 was 3, $89 was 9 and so forth. There it was. Adding
single digits added correctly.

Format is $s with no digits to the right of the decimal point. Please, what
is
happening here?

Wayne

 

[Jerry W. Lewis]

Excel is adding the data that you actually gave it, instead of the data
you intended to give it. Format as General, and you will probably see
fractions that you were not aware of, but which are contributing to your
total. You can get rid of them with the ROUND() function, or by
checking the Precision as displayed box at Tools|Options|Calculation.

Jerry

 

[Wayne G. Dengel]

Jerry:

I used: +round(sum(f1:f100),0) and came up with the same wrong answer.

No fractions were entered, only whole numbers. I formatted as General - saw
no change.

Kind of curious isn't it?

Wayne

 

[Ron Rosenfeld]

Jerry:

I used: +round(sum(f1:f100),0) and came up with the same wrong answer.

No fractions were entered, only whole numbers. I formatted as General - saw
no change.

Kind of curious isn't it?

Wayne

For what you are doing to work, you have to round BEFORE you add the numbers,
not after.

In your case, the formula should be *array-entered* and should read:

=SUM(ROUND(F1:F100,0))

To array-enter a formula, after typing or pasting it in, hold down
<ctrl><shift> while hitting <enter>. XL will place braces {...} around the
formula.


--ron

 

[Wayne G. Dengel]

Ron:

I entered =SUM(ROUND(F16:F227,0)) immediately followed by the Ctl-Shft >>
Enter keys.
The net formula shown is: {=SUM(ROUND(F16:F270,0))}
and the result is #VALUE!

Pls, Where did I mess up?

Wayne

 

[Wayne G. Dengel]

Curious to know/understand, since I entered whole $s only, how/why/where is
rounding necessary?

Wayne

 

[Ron Rosenfeld]

Curious to know/understand, since I entered whole $s only, how/why/where is
rounding necessary?

In an earlier post you wrote "Adding
single digits added correctly."

I must have misinterpreted that to mean that integers added OK.

Please post a list of integers that you have entered that produce an incorrect
answer with a simple SUM function.

The net formula shown is: {=SUM(ROUND(F16:F270,0))}
and the result is #VALUE!

Pls, Where did I mess up?

There is most likely a problem with your data. Somewhere in the range F16:F270
is some non-numeric data. Post back the results of these two formulas:

=COUNT(F16:F270)
=COUNTA(F16:F270)




--ron

 

[Wayne G. Dengel]

Ron:

Yes, in the noted range there are cells with alpha characters only. (And
some cells are totally empty.)

Does this - cells with alphas - tend to mess up the summation process?

And yes, I entered a column side-by-side to the one that is not working
where I entered ONLY, the units digits of the cells to be added. This one
added correctly.

Now this is sounding like the summation noted immediately above is the
correct way, data/integers entered correctly
and
the summation where alphas are included in the incorrect way.

Sounds like I have made a fundamental data entry error, expecting something
that is not correct.

BTW, =COUNT(F16:F227) >> 158
=COUNTA(F16:F227) >> 208

Wayne

 

[Ron Rosenfeld]

Ron:

Yes, in the noted range there are cells with alpha characters only. (And
some cells are totally empty.)

Does this - cells with alphas - tend to mess up the summation process?

And yes, I entered a column side-by-side to the one that is not working
where I entered ONLY, the units digits of the cells to be added. This one
added correctly.

Now this is sounding like the summation noted immediately above is the
correct way, data/integers entered correctly
and
the summation where alphas are included in the incorrect way.

Sounds like I have made a fundamental data entry error, expecting something
that is not correct.

BTW, =COUNT(F16:F227) >> 158
=COUNTA(F16:F227) >> 208

Wayne

Wayne,

The fog is lifting <g>.

The SUM function will work OK with alphas; however the ROUND function does not
and gives a #VALUE! error. That is why when you used the formula I gave you
got the error.

The reason for the error in your SUMS does have to do with rounding, and also
with the fact that Excel is limited to 15 decimal precision (as are most all
spreadsheets).

Knowing that there are alpha characters in your data, we can either modify the
formula, or you can modify your data.

To change the formula, again **array-enter** <ctrl><shift><enter>

=SUM(ROUND(IF(ISNUMBER(F16:F227),F16:F227,0),0))

Actually, you can round the numbers to whatever precision you wish (up to 15)
to match what your accountant did.

The formula above will substitute a '0' for any entry that is not numeric, so
the ROUND function will not produce an error.

It first ROUNDS your entries to the nearest dollar, and then SUMs the result.

If you wanted results to the penny, you would change the formula to:

=SUM(ROUND(IF(ISNUMBER(F16:F227),F16:F227,0),2))

again **array-entered**


--ron

 

[Wayne G. Dengel]

Ron: Found it!

Cells in column in question were the result of a formula in a neighboring
column.

In one cell, $o.50 was involved. This got automatically rounded when
displayed (no zeros to the right of the decimal point), but was added in the
summation process. The display correctly noted there was something less
than $1.oo being added!

Thank You for helping me discipline myself! (use the ROUND function to
assure inters are present when needed).

Best Regards,
Wayne