Duplicating a formula across cells, while only changing select values.

S Connelly
S Connelly used Ask the Experts™
on
Looking for a little help with another Google Sheet.

I have four columns where, for each row, I have set a pull down value (score) of 0 and 5. In the fifth column (ranking), I calculate a final value as a weighted average score with the following formula: =(sum(G3*G1,H3*H1,I3*I1,J3*J1)/SUM(G1:J1))*20

Where G1 - J1 contain the hidden % weighted values
And columns G3 to J3 (and lower) contain scored ranging from 0 - 5.

The *10 just expands the final value to a number that will allow for easier ranking.

Questions:

The most important question...
1. Is there a way to copy this formula down the ranking column and only increment values that are shown in bold (G3, H3, I3, J3)? As it stands, it seems that I need to manually enter this formula about 50x. :O

2. I would have used "AVERAGE.WEIGHTED(A1:A2, B1:B2)", but I couldn't figure out how to expand the final value.
    Is there a better way to calculate average weighted values and display a larger number than what you would normally see from input values ranging from 0 - 5?


Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
How about a sample file to ease out efforts?
For 1. try

=(sum(G3*G$1,H3*H$1,I3*I$1,J3*J$1)/SUM(G$1:J$1))*20

Putting a dollar on any part of the address would keep that part from changing
S ConnellyTechnical Writer

Author

Commented:
Wow! Thank you. I didn’t know about the $ trick. :)
You are welcome. I am not sure I understand part 2. Your formula looks good enough. Why do you want to change it?

If you want to do it through excel formulas This page shows how to use sum and sumproduct to get weighted averages. The scaling part is on you. You have to do it yourself.
S ConnellyTechnical Writer

Author

Commented:
Simple but educational solution. :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial