Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weighted Average - Copy Function
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weighted Average - Copy Function
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weighted Average - Copy Function
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weighted Average - Copy Function
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weighted Average - Copy Function
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weighted Average - Copy Function
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weighted Average - Copy Function
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
Show weighted average value after filter. | Excel Worksheet Functions | |||
copy a cell value not its function | Excel Discussion (Misc queries) | |||
Copy an IF function when Inserting new row | Excel Discussion (Misc queries) | |||
Using Average function when number is zero | Excel Worksheet Functions |