Weighted Average
 

On the first tab of my spreadsheet, I'd like to calculate the weighted
average of data on my 2nd tab.

The 2nd tab is setup using "Row()" formulas, and has auto-populated based on
predetermined criteria.
Currently, the 2nd tab has the following info in columns A, B & C:

Bank Principal Yield
Bk A $500 1.25%
Bk B $300 1.00%
Bk A $250 1.25%

I've tried using the following formula, but keep getting a #VALUE response,
and and not sure why?

=Sumproduct(--(A2:A5="Bk A"),B2:B5*C2:C5)

If I adjust the first formula to read =Sumproduct(--(A2:A5="Bk A"),B2:B5) I
get correct sum of principal amounts, but when I try to incorporate the
yields, I get the #VALUE message

Any ideas what I'm doing wrong?

Thanks in advance

Weighted Average
 

The formula looks fine, I suspect the problem is the format in Column C

Try a simple test and just add 1 to C2 =c2+1
if this gives you a #value error then you have found your problem.

My guess would be that the data in Column C is some kind of text and not a
number.
--
If this helps, please remember to click yes.


"Brian" wrote:

On the first tab of my spreadsheet, I'd like to calculate the weighted
average of data on my 2nd tab.

The 2nd tab is setup using "Row()" formulas, and has auto-populated based on
predetermined criteria.
Currently, the 2nd tab has the following info in columns A, B & C:

Bank Principal Yield
Bk A $500 1.25%
Bk B $300 1.00%
Bk A $250 1.25%

I've tried using the following formula, but keep getting a #VALUE response,
and and not sure why?

=Sumproduct(--(A2:A5="Bk A"),B2:B5*C2:C5)

If I adjust the first formula to read =Sumproduct(--(A2:A5="Bk A"),B2:B5) I
get correct sum of principal amounts, but when I try to incorporate the
yields, I get the #VALUE message

Any ideas what I'm doing wrong?

Thanks in advance

Weighted Average
 

"Brian" wrote:
=Sumproduct(--(A2:A5="Bk A"),B2:B5*C2:C5)

If I adjust the first formula to read =Sumproduct(--(A2:A5="Bk A"),B2:B5)
I get correct sum of principal amounts, but when I try to incorporate the
yields, I get the #VALUE message

First, you might write:

=Sumproduct(--(A2:A5="Bk A"),B2:B5,C2:C5)

Note the comma instead of "*".

Although I suspect that will eliminate the error, I also suspect the result
will be zero.

I wonder if the "percentages" are entered as text, not numbers. What does
TYPE(C2) return, for all of C2:C5?


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

"Brian" wrote in message
...
On the first tab of my spreadsheet, I'd like to calculate the weighted
average of data on my 2nd tab.

The 2nd tab is setup using "Row()" formulas, and has auto-populated based
on
predetermined criteria.
Currently, the 2nd tab has the following info in columns A, B & C:

Bank Principal Yield
Bk A $500 1.25%
Bk B $300 1.00%
Bk A $250 1.25%

I've tried using the following formula, but keep getting a #VALUE
response,
and and not sure why?

=Sumproduct(--(A2:A5="Bk A"),B2:B5*C2:C5)

If I adjust the first formula to read =Sumproduct(--(A2:A5="Bk A"),B2:B5)
I
get correct sum of principal amounts, but when I try to incorporate the
yields, I get the #VALUE message

Any ideas what I'm doing wrong?

Thanks in advance

Weighted Average
 

I guess that you've got text (rather than numbers) in some of the cells in
column C. Tht would explain the #VALUE! error, and also explains the slight
misalignment that you've got in the data you supplied. By default, text
aligns to the left and numbers to the right.
You can check with =ISTEXT(A2) and =ISNUMBER(A2) , each copied down the rest
of the rows. If you have a text string which includes a space [CHAR(32)],
the SUMPRODUCT formula may treat it as a number, but a text string with a
non-breaking space [CHAR(160)] will not be treated as a number.
--
David Biddulph

"Brian" wrote in message
...
On the first tab of my spreadsheet, I'd like to calculate the weighted
average of data on my 2nd tab.

The 2nd tab is setup using "Row()" formulas, and has auto-populated based
on
predetermined criteria.
Currently, the 2nd tab has the following info in columns A, B & C:

Bank Principal Yield
Bk A $500 1.25%
Bk B $300 1.00%
Bk A $250 1.25%

I've tried using the following formula, but keep getting a #VALUE
response,
and and not sure why?

=Sumproduct(--(A2:A5="Bk A"),B2:B5*C2:C5)

If I adjust the first formula to read =Sumproduct(--(A2:A5="Bk A"),B2:B5)
I
get correct sum of principal amounts, but when I try to incorporate the
yields, I get the #VALUE message

Any ideas what I'm doing wrong?

Thanks in advance

Weighted Average
 

Your formula works OK for me.

Are there any formula blanks in B:C?

Try the formula like this...

=SUMPRODUCT(--(A2:A5="Bk A"),B2:B5,C2:C5)

Using this syntax, it will ignore any text entries in B:C.

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
On the first tab of my spreadsheet, I'd like to calculate the weighted
average of data on my 2nd tab.

The 2nd tab is setup using "Row()" formulas, and has auto-populated based
on
predetermined criteria.
Currently, the 2nd tab has the following info in columns A, B & C:

Bank Principal Yield
Bk A $500 1.25%
Bk B $300 1.00%
Bk A $250 1.25%

I've tried using the following formula, but keep getting a #VALUE
response,
and and not sure why?

=Sumproduct(--(A2:A5="Bk A"),B2:B5*C2:C5)

If I adjust the first formula to read =Sumproduct(--(A2:A5="Bk A"),B2:B5)
I
get correct sum of principal amounts, but when I try to incorporate the
yields, I get the #VALUE message

Any ideas what I'm doing wrong?

Thanks in advance

Weighted Average
 

PS....

I wrote:
Although I suspect that will eliminate the
error, I also suspect the result will be zero.

I wonder if the "percentages" are entered as
text, not numbers. What does TYPE(C2) return,
for all of C2:C5?

I wrote the above in haste. Writing in haste again :-(.

I should have said: I suspect that some of the cells in B2:B5 and/or C2:C5
are text, not numbers. But that might not be wrong.

So changing the formula to the following will probably not only avoid the
#VALUE error, but it might also produce the desired result:

=Sumproduct(--(A2:A5="Bk A"),B2:B5,C2:C5)

Of course, if the result is not right, you do need check for "numbers" that
are really text.


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

"JoeU" <joeu2004 wrote in message
...
"Brian" wrote:
=Sumproduct(--(A2:A5="Bk A"),B2:B5*C2:C5)

If I adjust the first formula to read =Sumproduct(--(A2:A5="Bk A"),B2:B5)
I get correct sum of principal amounts, but when I try to incorporate the
yields, I get the #VALUE message

First, you might write:

=Sumproduct(--(A2:A5="Bk A"),B2:B5,C2:C5)

Note the comma instead of "*".

Although I suspect that will eliminate the error, I also suspect the
result will be zero.

I wonder if the "percentages" are entered as text, not numbers. What does
TYPE(C2) return, for all of C2:C5?


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

"Brian" wrote in message
...
On the first tab of my spreadsheet, I'd like to calculate the weighted
average of data on my 2nd tab.

The 2nd tab is setup using "Row()" formulas, and has auto-populated based
on
predetermined criteria.
Currently, the 2nd tab has the following info in columns A, B & C:

Bank Principal Yield
Bk A $500 1.25%
Bk B $300 1.00%
Bk A $250 1.25%

I've tried using the following formula, but keep getting a #VALUE
response,
and and not sure why?

=Sumproduct(--(A2:A5="Bk A"),B2:B5*C2:C5)

If I adjust the first formula to read =Sumproduct(--(A2:A5="Bk A"),B2:B5)
I
get correct sum of principal amounts, but when I try to incorporate the
yields, I get the #VALUE message

Any ideas what I'm doing wrong?

Thanks in advance

Weighted Average
 

Thanks Paul...
No luck....still #Value!

"Paul C" wrote:

The formula looks fine, I suspect the problem is the format in Column C

Try a simple test and just add 1 to C2 =c2+1
if this gives you a #value error then you have found your problem.

My guess would be that the data in Column C is some kind of text and not a
number.
--
If this helps, please remember to click yes.


"Brian" wrote:

On the first tab of my spreadsheet, I'd like to calculate the weighted
average of data on my 2nd tab.

The 2nd tab is setup using "Row()" formulas, and has auto-populated based on
predetermined criteria.
Currently, the 2nd tab has the following info in columns A, B & C:

Bank Principal Yield
Bk A $500 1.25%
Bk B $300 1.00%
Bk A $250 1.25%

I've tried using the following formula, but keep getting a #VALUE response,
and and not sure why?

=Sumproduct(--(A2:A5="Bk A"),B2:B5*C2:C5)

If I adjust the first formula to read =Sumproduct(--(A2:A5="Bk A"),B2:B5) I
get correct sum of principal amounts, but when I try to incorporate the
yields, I get the #VALUE message

Any ideas what I'm doing wrong?

Thanks in advance

Weighted Average
 

That did it!
Thanks!

"JoeU" wrote:

"Brian" wrote:
=Sumproduct(--(A2:A5="Bk A"),B2:B5*C2:C5)

If I adjust the first formula to read =Sumproduct(--(A2:A5="Bk A"),B2:B5)
I get correct sum of principal amounts, but when I try to incorporate the
yields, I get the #VALUE message

First, you might write:

=Sumproduct(--(A2:A5="Bk A"),B2:B5,C2:C5)

Note the comma instead of "*".

Although I suspect that will eliminate the error, I also suspect the result
will be zero.

I wonder if the "percentages" are entered as text, not numbers. What does
TYPE(C2) return, for all of C2:C5?


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

"Brian" wrote in message
...
On the first tab of my spreadsheet, I'd like to calculate the weighted
average of data on my 2nd tab.

The 2nd tab is setup using "Row()" formulas, and has auto-populated based
on
predetermined criteria.
Currently, the 2nd tab has the following info in columns A, B & C:

Bank Principal Yield
Bk A $500 1.25%
Bk B $300 1.00%
Bk A $250 1.25%

I've tried using the following formula, but keep getting a #VALUE
response,
and and not sure why?

=Sumproduct(--(A2:A5="Bk A"),B2:B5*C2:C5)

If I adjust the first formula to read =Sumproduct(--(A2:A5="Bk A"),B2:B5)
I
get correct sum of principal amounts, but when I try to incorporate the
yields, I get the #VALUE message

Any ideas what I'm doing wrong?

Thanks in advance




.