Sumproduct function not working

[Scott Summerlin]

In cell a1, I have the following sumproduct equation,
=sumproduct(b1:b5,b6:b10). The data in b1:b5 is formatted as currency. The
data in b6:b10 is percentages; the percentage values come from a link to
another worksheet.

The sumproduct functions works fine on the original worksheet. However, I
made 10 copies of original. For 4 of them, the sumproduct function does not
work. Cells b1:b5 have numbers greater than zero. The percentages in b6:b10
are 100%. Therefore, cell a1 should have a number greater than zero. Yet,
cell a1 shows only zero.

If I eliminate the links for the percentages in cells b6:b10 and instead
manually input the percentages, the sumproduct equation in cell a1 returns
the proper value.

I have two mysteries to solve:
1. How come the sumproduct equation works on some of the copied worksheets
but not all?
2. How come the sumproduct equation works if I convert the percentages from
linked data to manually inputted data?

Thanks,
Scott

 

[JE McGimpsey]

SUMPRODUCT() will treat text as 0. Are your linked formulas producing
text?

Or are you pasting your data in, say from a web site, where they'll be
parsed as text (to fix, copy an empty cell, select your numbers, choose
Edit/Paste Special, selecting the Values and Add radio buttons).

 

[Frank Kabel]

Hi
does the following work:
=sumproduct(--b1:b5,--b6:b10)
If yes, then your values are stored as text

--
Regards
Frank Kabel
Frankfurt, Germany

im Newsbeitrag
news:[email protected]...

 

[Scott Summerlin]

Hi,

The linked formula cells are formatted as percentages not text. When I
manually paste the data (instead of relying on the links), I'm copying it
from another worksheet in the same workbook. The original data is formatted
as a percentage as is the cells I paste it to.

Thanks,
Scott

 

[Scott Summerlin]

Hi Frank,

Yes, your suggestion works. This still leaves me perplexed on two issues:
1. None of the cells are formatted as text. b1 is currency, b6 is
percentage. How come Excel is reading them as text?
2. How come the equation works in some copies of the worksheet but not all?

Thanks,
Scott

 

[Frank Kabel]

Hi
depends how you have created your original values. Have you imported
them?

--
Regards
Frank Kabel
Frankfurt, Germany

im Newsbeitrag

 

[Scott Summerlin]

Frank,

The original values--both the currency and percentages--come from a Essbase,
which is a financial database system. That's obviously the problem. When
Essbase displays values in Excel, the values in each cell are unformatted
i.e. they don't display as currency or percentages, they simply display as
text. So even though I've changed the formatting of the cells, Excel must be
ignoring my formatting and reading the underlying Essbase formatting.
Interesting. Thanks so much for your assistance.

Regards,
Scott

 

[hrlngrv - ExcelForums.com]

Scott Summerlin wrote..

The original values--both the currency and percentages--come fro

Essbase, which is a financial database system. That's obviousl th
problem. When Essbase displays values in Excel, the values i each cel
are unformatted i.e. they don't display as currency o percentages, the
simply display as text. So even though I've changed th formatting o
the cells, Excel must be ignoring my formatting and reading th
underlying Essbase formatting. Interesting. Thanks so much fo you
assistance

This implies you're missing a fundamental point Frank and J.E. trie
to raise but weren't sufficiently explicit in stating

FORMATTING HAS NO EFFECT ON VALUE

If you have text that appears like 123, e.g., produced by the formul
="123", you can change its number format to anything you want, bu
it'll remain text, and thus SUMPRODUCT will continue to threat it a
zero. That's why Frank's suggestion of adding the -- tokens worked
It converted your text ranges to numeric arrays

 

[Frank Kabel]

Hi
try the following:
- select an empty cell and copy it
- select your imported values
- goto 'Edit - Paste Special' and choose 'Add'

 

[Scott Summerlin]

Hi,

I got the point Frank and JE were making. What I didn't get, but you
explained it, is that the "--" converted the text to numeric arrays.

Thanks,
Scott

 

[Scott Summerlin]

Danke sehr!

Have a great weekend.

Scott

Hi
try the following:
- select an empty cell and copy it
- select your imported values
- goto 'Edit - Paste Special' and choose 'Add'

 

[JE McGimpsey]

What I didn't get, but you explained it, is that the "--" converted
the text to numeric arrays.

For an explanation of how, see

http://www.mcgimpsey.com/excel/doubleneg.html

 

[Scott Summerlin]

Hi JE,

Thanks for the link and explanation. I'm bookmarking your site.

Have a great weekend,
Scott