Calculate average of ratings as a function of length

[Guest]

I have roadway segments that have been rated 1-5. Each segment has a
different length. I want to calculate the average rating for the total
length. How do I do that and make it a function of length. example:
road A has a rating of 5 and is 3 miles long
road B has a rating of 3 and is 1 miles long
road C has a rating of 2 and is 2 miles long

What is the average rating?

 

[Guest]

3.666666667

 

[Bernard Liengme]

This is called a 'weighted average'
For your data use (3*5 + 1*3 + 2*2) / (3+1+2) to get average rate
You could have a column next to miles and rates to compute miles*rate
then add this and divide by sum(miles)
But it can be done in one cell: If miles are in A1:A3, rates in B1:B3 the
formula
=SUMPRODUCT(A1:A3,B1:B3)/SUM(A1:A3)
will give the answer
best wishes