Speed controls, how to calculate average speed.

Im an intern workin with taking speed controls on public roads, and doing a median value of it is very intresting as you can guess.


hm.png

left side is the speed, and the right side is total number of cars within the range.


I can not figure out how excel can calculate this automaticly.


Any ideas?


-R

Assuming your data is in the same position as Ali suggests (B5:C19), then perhaps this would work?

=SUMPRODUCT((SUBSTITUTE(B5:B19,"<=",""))*(C5:C19))/SUM(C5:C19)

Combined total of speeds * cars (74,510) divided by total of cars (1,838) = 40.5 km/h.

Of course the number isn't truly accurate as there's a 5km/h leeway in each speed bracket, meaning everyone in the <=35 bracket for example could all be travelling at 31 and that will affect the figure.

My maths could be completely incorrect altogether, but without an example output it's difficult to know what you're expecting.

BSB

Maybe this?

=SUMPRODUCT((SUBSTITUTE(B5:B19,"<=","")-2.5)*(C5:C19))/SUM(C5:C19)

By adding the part in red it will calculate the average based on the middle value of each speed bracket and results in 37.5.

BSB

I realised that the problem was the language, so I changed it to =PRODUKTSUMMA((BYT.UT(O6:O20,"<=","")-2.5)*(P6:P20))/SUMMA(P6:P20)
And it seems to work, avg 37.5 is close enough, gonna try it on more examples to see the difference.

Problem is, Im gonna fill up more space i.e perhaps next time someone is going over 80 / 85 and so on, and i wanna ignore all empty spaces so I dont get "incorrect values"

Or else I have to manually adjust it every time, sometimes it is more, some times its less.


-R

You could use "dynamic named ranges" that will expand/shrink as you add/remove data.

See attached and pres Ctrl+F3 to open the named range manager. The named range formulas may need amending slightly depending on the layout of your actual file.

Or you could use structured tables.

BSB

Would it be possible to see a copy of your file with no sensitive data included?

If you can highlight the area you'd put this data in then perhaps I can help you set up the dynamic named ranges.

BSB

If your speed brackets column just said 5, 10, 15 etc. rather than <=5, <=10 etc then the formula would work even with blank rows.

=PRODUKTSUMMA((O6:O20-2.5)*(P6:P20))/SUMMA(P6:P20)

Just change the 20s in the formula to whatever your maximum row number would be

BSB

Yes, I understand.
Thing is this info is copypasted from an 3rd party app and it is not possible to choose the content your copying, brackets included. :/

Just wanted also to say thanks, for solving the originally asked question.

I have more math equations and things that needs to be solved if you are intrested?

-R

Originally Posted by AliGW

Here's the Swedish version of my suggestion:

=INDEX($B$5:$B$19;PASSA(MEDIAN($C$5:$C$19);$C$5:$C$19;0))

Thank you for effort, now that BSB has done alot, and also he figured out to get correct info also using his math skills, im gonna use that

Originally Posted by Hejhallo

I have more math equations and things that needs to be solved if you are intrested?

If they're not directly related to the question in this thread then feel free to start new threads for your other questions and we'll do what we can to help.

BSB