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
|