Weighted Average with multiple variables

**bigtoad** · 02-26-2011, 07:54 PM

Hey all,

So I am trying to calculate avg. cost per customer contacted by mail. The stats look like this:

126 2895 $9.04 1
104 724 $2.72 2
164 685 $1.65 5
a b c d

A=number of customers in segment
B=size of segment
C=Cost to reach actual customer in segment(at .55 cost per mail piece)
D=Number of pieces mailed to segment

So getting C for each segment is easy its just =((B1)*(.55))/R1

but figuring out the weighted average for C of the whole mail plan when each D is different is incredibly frustrating.

Any ideas?

**HSV** · 02-26-2011, 08:10 PM

Array formula

Please Login or Register  to view this content.

After copy Ctrl+Shift+Enter

Or:

Please Login or Register  to view this content.

just Enter.

**daddylonglegs** · 02-26-2011, 08:14 PM

I'm not sure what this formula represents

=((B1)*(.55))/R1

wouldn't you multiply by the number sent to each segment in D1? What's in R1 you don't mention that?

Assuming you want a weighted cost for C given numbers in D try

=SUMPRODUCT(C2:C10,D2:D10)/SUM(D2:D10)

**bigtoad** · 02-26-2011, 08:55 PM

Sorry, R1=A1

So the formula "=SUMPRODUCT(C2:C10,D2:D10)/SUM(D2:D10)" and all D=1 gives me what seems like a reasonable number(1.39)....BUT.....the formula "=((SUM(B1:B3))*(.55))/SUM(A1:A3)" gives a different one(1.22) even though it should be the same as long as all D's=1.

What am I missing?

**daddylonglegs** · 02-27-2011, 04:07 PM

If every entry in column D is 1 then the weighted average is the same as a straight average of column C so this would give the same result

=AVERAGE(C2:C10)

You can't get the equivalent of that by using

=SUM(B2:B10)*0.55/SUM(A2:A10)

you'd have to use an "array formula" to get the equivalent, i.e.

=AVERAGE(B2:B10*0.55/A2:A10)

confirmed with CTRL+SHIFT+ENTER

See the attached where if column D are all 1s the 3 different formulas in G2:G4 will all give the same results. G3 and G4 will always be the same but G2 will differ if D2:D10 has different results. Press F9 to generate some different random values