Dee has attended:
Excel Advanced course
Excel Intermediate course
Stats in Excel
I am working with a number of very large spreadsheets. The data in the columns are vehicle speeds. The columns are headed for the hour in a 24 hour day. For what we need, the hour does not count, just all the recordings for speed. I can sort the average, but I also need to get the 85th percentile from all the speeds, plus the percent above the average and the percent greater than 15 mph over the average. I'm probably being braindead here, but I can't find a way to get this data from the cummulative set of numbers across 24 columns. Do I need to slog it out with each individual column, or should I move everything into one column? I keep thinking there is an easier way to do this.
Cheers,
Dee
RE: stats in Excel
Hello Dee
Thank you for your question and apologies for the delay in response.
Have you got a sample spreadsheet you can send through so we can have a look at what you're working with? If so my email address is amanda@stl-training.co.uk
Kind regards
Amanda
RE: stats in Excel
Hello Dee
Thanks for sending your spreadsheet in.
Please find attached the spreadsheet with the results I think you are after. I named the range of cells containing the figures as speeds, just to make things a bit easier when putting in the formulas. I'm not a statistician, but I think the function I've used to get the 85th percentile is giving the right result, but I'll leave that up to you to judge :)
Hope this helps.
Kind regards
Amanda
Microsoft Training question response.xls
