Weighted Average - Copy Function

[Melissa]

I am having hard time utilizing the copy function. The top row is my header
row. The issue is that if i do weighted average
=sumproduct(b2:b5,c2:c5)/sum(c2:c5) in column d5, then I copy cell d5 and
past in column d7, it keeps the same number of cells (in this case would be 4
- instead of 2). I tried to subtotal first separating at every service and
totaling at the bottom of each service and a page break between
services...then do weighted average in the actual C column...but it still did
the same thing.

Service Cost Record Count
Federal Search $1 10
Federal Search $2 10
Federal Search $5 20
Federal Search $2 15
State Search $5 30
State Search $5 30
County Search $3 15

 

[Ron Coderre]

If you want the formulas to always include row_2, then try this:
D5=sumproduct(b$2:b5,c$2:c5)/sum(c$2:c5)
Then copy down as far as you need.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Melissa]

I need to find the weighted cost of each service. My problem is my
spreadsheet is 7000 rows long. And, there may be 5 rows for Federal, 3 rows
for State, and 10 rows for County...and i don't know how to take into acount
the different number of rows in a formula. That is why i tried to subtotal
first, then do weight average, but it still kept the same number of rows for
each service regardless if they were different.

 

[Melissa]

In other words, I want to subtotal....divide at every service name change and
then do a weighted average on two columns. However, it appears my only
options when subtotalling is one function per column (and sumproduct isn't an
option). Thanks for your help!!

 

[Ron Coderre]

See if this gets you any closer:

E2:
=IF(A3<>A2,SUMPRODUCT(--($A$2:$A$8=A2)*$C$2:$C$8*$D$2:$D$8)/SUMIF($A$2:$A$8,A2,$D$2:$D$8),"")

Copy that formula down through E8.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Melissa]

It didn't work. Is it possible to attach a spreadsheet to this message board?

 

[Ron Coderre]

How about this...post the formulas you would use to only get the weighted
average for the "Federal Search" category. Don't use SUMPRODUCT. Based on
those formulas we should be able to work out what you need.

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Melissa]

Humm. Not sure how to do weighted average without sumproduct? Sorry, I
guess I need more Excel classes?? Here are my first 14 rows. In column A is
Service Name, in column B is Service Cost, and in Column C is Record Count.

Service Name Service Cost Record Count
Abuse Registry Submission 0.01 2
Abuse Registry Submission 0.01 40
Abuse Registry Submission 0.01 442
Abuse Registry Submission 0.01 13
Abuse Registry Submission 1 233
Abuse Registry Submission 0.01 52
Abuse Registry Submission 0 1
Abuse Registry Submission 0.01 5
Abuse Registry Submission 0.01 10
Abuse Registry Submission 0.01 96
Abuse Registry Submission 0.01 220
Appeal Court Search 0 199
Attorney General/State Consumer Affairs 0.01 20