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

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" wrote:

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

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.

"Ron Coderre" wrote:

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" wrote:

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

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!!

"Melissa" wrote:

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.

"Ron Coderre" wrote:

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" wrote:

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

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" wrote:

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!!

"Melissa" wrote:

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.

"Ron Coderre" wrote:

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" wrote:

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

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

"Ron Coderre" wrote:

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" wrote:

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!!

"Melissa" wrote:

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.

"Ron Coderre" wrote:

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" wrote:

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