85th Percentile Speed Survey

[c.jones]

Hello,

I work in traffic management and are looking to find the 85th percentile from a data set.

I have attached an example of the data we have from our equipment. Each row is a period of time on a certain day along with the total number of vehicles for that period along with the number of vehicle travelling in a particular speed bracket - <5, <10, <15 etc which is separated into rows.

I'm assuming I will need to manipulate the data here slightly into different format in order to calculate the overall 85th. I'm thinking probably needing another column for '85th%' and then working out each 85% percentile for each row (time period) and then working out the overall 85th percentile from each 85th? I apologise if I have babbled.

Any assistance will be greatly appreciated!

Thank you

[Glenn Kennedy]

Unfortunately, you're right. You haven't got enough detail here to get an accurate estimate of the percentile.

If you look at the sheet, you'll see that the 85% percentile coincides with the columns with the maximum number of values per bin. So, I guess my Q is... do you have the underlying non-binned data?

Any reason why you're still using the obsolete .xls file format?

[c.jones]

Thank you for your response. I do have a datasets with hundreds of columns but scaled it down for sharing here as per the instruction at the top of the page.

Forgive my tardiness but from the example you have shared - the highest percentage in the red cells is 98% therefore does this mean the 85th percentile speed is 30 mph.

The data was extracted from hardware which generated the file automatically

[Glenn Kennedy]

I guess it comes down to what your real data are. If they are car speeds, then stating that the 85th Percentile is in the range 25-30 is probably close enough. If they are REALLY something where greater precision is required, then it may not be. You'd be surprised at the analogies people use to describe their real data in abstract terms!!

i just noticed thta you say you work in traffic management... so I guess that 25-30 will do fine for you.

[c.jones]

Thank you - yeah, 25-30 would be useful for me. I have attached another sheet with a greater dataset with the full data.

So to confirm for my lack of understanding, if I replicated those formulas to SUM the entire dataset - where would I then look to find the 85th? Apologies again for being dense.

[Glenn Kennedy]

Use:
=SUM($D2:D11)/SUM($D2:$Q11)

see sheet.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[AliGW]

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.