Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Scott Summerlin
 
Posts: n/a
Default Sumproduct function not working

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
  #2   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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).

In article ,
Scott Summerlin <Scott wrote:

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?

  #4   Report Post  
Scott Summerlin
 
Posts: n/a
Default

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

"JE McGimpsey" wrote:

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).

In article ,
Scott Summerlin <Scott wrote:

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?


  #5   Report Post  
Scott Summerlin
 
Posts: n/a
Default

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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Scott Summerlin" <Scott schrieb
im Newsbeitrag
...
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





  #6   Report Post  
Frank Kabel
 
Posts: n/a
Default

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Scott Summerlin" <Scott schrieb
im Newsbeitrag
...
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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Scott Summerlin" <Scott

schrieb
im Newsbeitrag
...
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




  #7   Report Post  
Scott Summerlin
 
Posts: n/a
Default

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

"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Scott Summerlin" <Scott schrieb
im Newsbeitrag
...
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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Scott Summerlin" <Scott

schrieb
im Newsbeitrag
...
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




  #8   Report Post  
hrlngrv - ExcelForums.com
 
Posts: n/a
Default

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!
  #9   Report Post  
Frank Kabel
 
Posts: n/a
Default

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Scott Summerlin" schrieb im
Newsbeitrag ...
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

"Frank Kabel" wrote:

Hi
depends how you have created your original values. Have you

imported
them?

--
Regards
Frank Kabel
Frankfurt, Germany

"Scott Summerlin" <Scott

schrieb
im Newsbeitrag
...
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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Scott Summerlin" <Scott

schrieb
im Newsbeitrag
...
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





  #10   Report Post  
Scott Summerlin
 
Posts: n/a
Default

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!



  #11   Report Post  
Scott Summerlin
 
Posts: n/a
Default

Danke sehr!

Have a great weekend.

Scott

"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Scott Summerlin" schrieb im
Newsbeitrag ...
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

"Frank Kabel" wrote:

Hi
depends how you have created your original values. Have you

imported
them?

--
Regards
Frank Kabel
Frankfurt, Germany

"Scott Summerlin" <Scott

schrieb
im Newsbeitrag
...
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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Scott Summerlin" <Scott

schrieb
im Newsbeitrag
...
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






  #12   Report Post  
JE McGimpsey
 
Posts: n/a
Default

In article ,
Scott Summerlin wrote:

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
  #13   Report Post  
Scott Summerlin
 
Posts: n/a
Default

Hi JE,

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

Have a great weekend,
Scott

"JE McGimpsey" wrote:

In article ,
Scott Summerlin wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Maskerr function not working on my new computer - help? Russo Excel Worksheet Functions 1 December 1st 04 12:03 AM
Sum function not working correctly in Excel (Skips Cell) Tony Excel Worksheet Functions 5 November 30th 04 12:52 AM
adding two sumproduct formulas together ski2004_2005 Excel Worksheet Functions 1 November 12th 04 10:08 PM
Replace function not working properly in Excel 2000 SP3 rgbytg Excel Worksheet Functions 5 November 11th 04 04:44 PM
COUNTA Function not working =COUNTA(C3:C69,"NH") MikeinNH Excel Worksheet Functions 2 November 8th 04 02:19 AM


All times are GMT +1. The time now is 10:36 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"