find highest values (speeds) by day and hour

breilly00

New Member
Joined
Sep 15, 2008
Messages
48
Extracting data from an electronic speed control sign.
the Raw data extracted can be narrowed down to date/time and speed columns. The times will be unique to the HH:MM:SS. Narrowed down data sample below
datespeed
4/1/2021 9:48​
66​
4/1/2021 11:13​
66​
4/1/2021 15:29​
66​

Each month the data will be extracted from the sign and loaded into Excel. We are trying to find out on what days the highest speeding occurs and if most speeding occurs at a certain. hour. I can extract the hour and day from the time column. But, cannot find a sample excel graph to use (if that is even possible). Do I need formuls, pivot tables, etc. to be able to answer the following to questions.

In a given month
what days (1-30) of the month recorded the highest speeds - sorted by high low
what hour (1-24) of the month recorded the highest speeds - sorted high to low

knowing this we can put traffic patrol on the roads during those days/times

sample of what I can get my excel sheet to look like.
datespeedDayHourSpeed
4/1/2021 9:48​
66​
1​
9​
66​
4/1/2021 11:13​
66​
1​
11​
66​
4/1/2021 15:29​
66​
1​
15​
66​
4/1/2021 17:03​
66​
1​
17​
66​
4/2/2021 9:14​
62​
2​
9​
62​
4/2/2021 10:06​
66​
2​
10​
66​
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Saba Sabaratnam

Active Member
Joined
May 26, 2018
Messages
397
Office Version
  1. 365
  2. 2010
Based on the limited data that you provided and assuming that you are using Excel 365, you could use SORTBY function as shown below.

Enter the following formula in I2 to sort speed by date

=SORTBY($A$1:$B$7,B1:B7,-1)

Enter the following formula in L2 to sort speed by hour.

=SORTBY(E1:F7,F1:F7,-1)

Kind regards

Saba


1622513466125.png

=
 
Solution

breilly00

New Member
Joined
Sep 15, 2008
Messages
48
gently dropping 0's and 1's that translate to WONDERFUL to the southern neighbor. TY SABA --- stay safe and have no worries.
 

Forum statistics

Threads
1,141,707
Messages
5,707,985
Members
421,539
Latest member
zuniBM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top