Incorrect Total in Excel 2007

  • Thread starter Thread starter Dana F. Brewer
  • Start date Start date
D

Dana F. Brewer

I have a spreadsheet which checks to see if a field has data in it and then
does a simple multiplication. Then I have a simple sum of all the results in
the results column. I have another set of numbers doing the same thing. The
results all seem to be perfect. The problem is when I add the two sums. I
get a number that appears to be incorrect. I know excel is binary and may
not 'see' the numbers as I see them but I'd like to know how to make it treat
the numbers literally and add them accordingly.

Here's a sample of the sheet:

COLUMN C (looks at one sheet and copies the value that has been data-entered):
=IF(ISBLANK('Finance Worksheet'!E2),"",'Finance Worksheet'!E2)


COLUMN D (fixed number data-entered)


COLUMN E (which has about 15-20 rows that either have or don't have numbers
in them):
=IF(ISNUMBER(C20),((C20*D20)/1000),)

and then further down in the column:
=SUM(E20:E34)

My subtotal is 5092.75.


COLUMN F (just like in column e):
=IF(ISNUMBER(D41),((D41*E41)/1000),)

and then further down in the column:
=SUM(F41:F79)

My subtotal is 3439.22.


Then:
=E36+F81

My expected result is 8531.97 but I get 8531.96.

Please HELP!!!!!!
 
Dana F. Brewer said:
=SUM(E20:E34)
My subtotal is 5092.75.
[....]
=SUM(F41:F79)
My subtotal is 3439.22.

Then:
=E36+F81
My expected result is 8531.97 but I get 8531.96.
Click to expand...

If you format E36 and F81 with more decimal places, you will probably see
that they are not really 5092.75 and 3439.22.

I'd like to know how to make it treat
the numbers literally and add them accordingly.
Click to expand...

Since it appears that you are working with financial data, I would suggest
the following, at a minimum:

=ROUND(SUM(E20:E34), 2)

=ROUND(SUM(F41:F79), 2)

=ROUND(E36+F81, 2)

Quite likely, you also want to do the following:

=IF(ISNUMBER(C20), ROUND((C20*D20)/1000,2) ,0)

=IF(ISNUMBER(D41), ROUND((D41*E41)/1000,2), 0)

Whether or not to round intermediate results does depend on your intent and
the rules that might apply to your application.

Even if you round intermediate results, it is good practice to round even
simple arithmetic operations like E36+F81 and certainly column operations
like SUM(E20:E34).

If you don't, you might be surprised by examples like this:

=IF(10.1 - 0.1 = 0.1, TRUE).

That results in FALSE(!). One way to correct that is:

=IF(ROUND(10.1 - 0.1, 1) = 0.1, TRUE).
 
Thanks so much Joe User. Your answer was simple yet thorough and gave me the
information I needed to correct my formulas, resolve my problem, and what to
think about for next time.

You are awesome!

....Dana :)

Joe User said:
Dana F. Brewer said:
=SUM(E20:E34)
My subtotal is 5092.75.
[....]
=SUM(F41:F79)
My subtotal is 3439.22.

Then:
=E36+F81
My expected result is 8531.97 but I get 8531.96.
Click to expand...

If you format E36 and F81 with more decimal places, you will probably see
that they are not really 5092.75 and 3439.22.

I'd like to know how to make it treat
the numbers literally and add them accordingly.
Click to expand...

Since it appears that you are working with financial data, I would suggest
the following, at a minimum:

=ROUND(SUM(E20:E34), 2)

=ROUND(SUM(F41:F79), 2)

=ROUND(E36+F81, 2)

Quite likely, you also want to do the following:

=IF(ISNUMBER(C20), ROUND((C20*D20)/1000,2) ,0)

=IF(ISNUMBER(D41), ROUND((D41*E41)/1000,2), 0)

Whether or not to round intermediate results does depend on your intent and
the rules that might apply to your application.

Even if you round intermediate results, it is good practice to round even
simple arithmetic operations like E36+F81 and certainly column operations
like SUM(E20:E34).

If you don't, you might be surprised by examples like this:

=IF(10.1 - 0.1 = 0.1, TRUE).

That results in FALSE(!). One way to correct that is:

=IF(ROUND(10.1 - 0.1, 1) = 0.1, TRUE).








Dana F. Brewer said:
I have a spreadsheet which checks to see if a field has data in it and then
does a simple multiplication. Then I have a simple sum of all the results
in
the results column. I have another set of numbers doing the same thing.
The
results all seem to be perfect. The problem is when I add the two sums.
I
get a number that appears to be incorrect. I know excel is binary and may
not 'see' the numbers as I see them but I'd like to know how to make it
treat
the numbers literally and add them accordingly.

Here's a sample of the sheet:

COLUMN C (looks at one sheet and copies the value that has been
data-entered):
=IF(ISBLANK('Finance Worksheet'!E2),"",'Finance Worksheet'!E2)


COLUMN D (fixed number data-entered)


COLUMN E (which has about 15-20 rows that either have or don't have
numbers
in them):
=IF(ISNUMBER(C20),((C20*D20)/1000),)

and then further down in the column:
=SUM(E20:E34)

My subtotal is 5092.75.


COLUMN F (just like in column e):
=IF(ISNUMBER(D41),((D41*E41)/1000),)

and then further down in the column:
=SUM(F41:F79)

My subtotal is 3439.22.


Then:
=E36+F81

My expected result is 8531.97 but I get 8531.96.

Please HELP!!!!!!
Click to expand...

.
Click to expand...
 
Dana F. Brewer said:
Thanks so much Joe User.
Click to expand...

You're welcome. I'm glad you were able to understand despite my typo.
=IF(10.1 - 0.1 = 0.1, TRUE).
Click to expand...

Of course, that should be:

=IF(10.1 - 10 = 0.1, TRUE)


----- original message -----

Dana F. Brewer said:
Thanks so much Joe User. Your answer was simple yet thorough and gave me
the
information I needed to correct my formulas, resolve my problem, and what
to
think about for next time.

You are awesome!

...Dana :)

Joe User said:
Dana F. Brewer said:
=SUM(E20:E34)
My subtotal is 5092.75.
[....]
=SUM(F41:F79)
My subtotal is 3439.22.

Then:
=E36+F81
My expected result is 8531.97 but I get 8531.96.
Click to expand...

If you format E36 and F81 with more decimal places, you will probably see
that they are not really 5092.75 and 3439.22.

I'd like to know how to make it treat
the numbers literally and add them accordingly.
Click to expand...

Since it appears that you are working with financial data, I would
suggest
the following, at a minimum:

=ROUND(SUM(E20:E34), 2)

=ROUND(SUM(F41:F79), 2)

=ROUND(E36+F81, 2)

Quite likely, you also want to do the following:

=IF(ISNUMBER(C20), ROUND((C20*D20)/1000,2) ,0)

=IF(ISNUMBER(D41), ROUND((D41*E41)/1000,2), 0)

Whether or not to round intermediate results does depend on your intent
and
the rules that might apply to your application.

Even if you round intermediate results, it is good practice to round even
simple arithmetic operations like E36+F81 and certainly column operations
like SUM(E20:E34).

If you don't, you might be surprised by examples like this:

=IF(10.1 - 0.1 = 0.1, TRUE).

That results in FALSE(!). One way to correct that is:

=IF(ROUND(10.1 - 0.1, 1) = 0.1, TRUE).








Dana F. Brewer said:
I have a spreadsheet which checks to see if a field has data in it and
then
does a simple multiplication. Then I have a simple sum of all the
results
in
the results column. I have another set of numbers doing the same
thing.
The
results all seem to be perfect. The problem is when I add the two
sums.
I
get a number that appears to be incorrect. I know excel is binary and
may
not 'see' the numbers as I see them but I'd like to know how to make it
treat
the numbers literally and add them accordingly.

Here's a sample of the sheet:

COLUMN C (looks at one sheet and copies the value that has been
data-entered):
=IF(ISBLANK('Finance Worksheet'!E2),"",'Finance Worksheet'!E2)


COLUMN D (fixed number data-entered)


COLUMN E (which has about 15-20 rows that either have or don't have
numbers
in them):
=IF(ISNUMBER(C20),((C20*D20)/1000),)

and then further down in the column:
=SUM(E20:E34)

My subtotal is 5092.75.


COLUMN F (just like in column e):
=IF(ISNUMBER(D41),((D41*E41)/1000),)

and then further down in the column:
=SUM(F41:F79)

My subtotal is 3439.22.


Then:
=E36+F81

My expected result is 8531.97 but I get 8531.96.

Please HELP!!!!!!
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top