Weighted Average

[Bob@Sun]

Hello,

Can anybody help me with altering a little the following function:

I am using sumproduct to find Weighted Average if my data is arragned like this:

Cost Number of Items
100 : 5
200 : 2
300 : 3

the formula =SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4) is working great.

As I have my data arranged differently, I was wondering if I can change the formula.

My data is like this:
Cost Item Cost Item Cost Item
100 5 200 2 300 3

How should I change the formula in this case?

[DonkeyOte]

You would use something like:

=SUMPRODUCT(MOD(COLUMN($A2:$E2),2),$A2:$E2,$B2:$F2)/SUMIF($A$1:$F$1,"Item",$A2:$F2)

[Bob@Sun]

Hello,

I have faced a very strange formula behavior.

In the attached file you can see that I have exactly the same formula but when applied on different place on the sheet I get different result

Any ideas why is that??????

[daddylonglegs]

By using MOD and COLUMN in conjuction you are getting the result based on whether the columns are "even" or "odd", Because one of your tables starts in an "odd" column and the other in an "even" column that won't work. Perhaps try this version in B4

=SUMPRODUCT((A1:C1="Cost")+0,A2:C2,B2:D2)/SUMIF(A1:D1,"Item",A2:D2)

If you copy to G4 it'll still work

[Bob@Sun]

Thanks a lot!

This worked.