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
S ConnellyTechnical WriterAsked:
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.

Saqib Husain, SyedEngineerCommented:
How about a sample file to ease out efforts?
Saqib Husain, SyedEngineerCommented:
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

Your issues matter to us.

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

Start your 7-day free trial
S ConnellyTechnical WriterAuthor Commented:
Wow! Thank you. I didn’t know about the $ trick. :)
Saqib Husain, SyedEngineerCommented:
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 WriterAuthor Commented:
Simple but educational solution. :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Google

From novice to tech pro — start learning today.