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

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
LVL 4
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EngineerCommented:
How about a sample file to ease out efforts?
EngineerCommented:
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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Technical WriterAuthor Commented:
Wow! Thank you. I didn’t know about the \$ trick. :)
EngineerCommented:
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.
Technical WriterAuthor Commented:
Simple but educational solution. :)