Weighted Average - Copy Function

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

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

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

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

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


"Ron Coderre" wrote:

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