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

"hrlngrv - ExcelForums.com" wrote:

Scott Summerlin wrote...
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.

This implies you're missing a fundamental point Frank and J.E. tried
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 formula
="123", you can change its number format to anything you want, but
it'll remain text, and thus SUMPRODUCT will continue to threat it as
zero. That's why Frank's suggestion of adding the -- tokens worked.
It converted your text ranges to numeric arrays.
---------
www.coffeecozy.com

Use your Bodum and give up cold coffee for good!