Weighted Average

[Brian]

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

 

[Paul C]

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.

 

[JoeU]

=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 -----

 

[David Biddulph]

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.

 

[T. Valko]

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.

 

[JoeU]

PS....

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

 

[Brian]

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

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.

 

[Brian]

That did it!
Thanks!

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




.