Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Sumproduct and Weighted Average

0 views
Skip to first unread message

Alohajg

unread,
Mar 12, 2003, 2:53:58 PM3/12/03
to
Hi,

I wanted to calculate the weighted Average of Itemcode CK and post it in
Cell D2 using Worksheet function in my spreadsheet which has the following
data ( 6 Rows and 4 Columns) ( The 1st Row is the Header information). I
tried using the conditional SUMPRODUCT Formula in Cell D2 and it came up
with a VALUE# Error .

=SUMPRODUCT(($A$2:$A$6=A$2)*$B$2:$B$6,$C$2:$C$6))/SUMPRODUCT(($A$2:$A$6=A$2)
*($B$2:$B$6))

A B C D
1 Code Pieces Prices Wtd Average

2 CK 20 20.39

3 CH 30 30.9

4 LO 20 20.35

5 CK 30 20.5

6 CK 20 20.39

Can Anyone Please help. If this is not possible using Worksheet Functions
can anyone help me to get the result with with VBA code please.

Thanks in advance,
Alohajg
jg65...@emirates.net.ae

PC

unread,
Mar 12, 2003, 4:01:42 PM3/12/03
to

=SUMPRODUCT((A2:A10="CK")*(B2:B10)*(C2:C10))/SUMPRODUCT
(C2:C10,B2:B10)

Would return the weighted average sales for CK

HTH
PC

>.
>

0 new messages