Weighted Average

  • Thread starter Thread starter Brian
  • Start date Start date
B

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
 
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 said:
=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
Click to expand...

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 -----
 
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.
 
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.
 
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?
Click to expand...

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 -----
 
Thanks Paul...
No luck....still #Value!

Paul C said:
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.
Click to expand...
 
That did it!
Thanks!

JoeU said:
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 -----




.
Click to expand...
 
Back
Top