# find highest values (speeds) by day and hour

#### breilly00

##### New Member
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
 date speed 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.
 date speed Day Hour Speed 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.

#### bob33

##### Board Regular
Easier if you share an example file.

#### breilly00

##### New Member
Easier if you share an example file.
Bob,

I know that there is a way to share the file; but, it evades me. How could I get the file to you???

#### Saba Sabaratnam

##### Active Member
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

=

#### breilly00

##### New Member
gently dropping 0's and 1's that translate to WONDERFUL to the southern neighbor. TY SABA --- stay safe and have no worries.

Replies
6
Views
395
Replies
15
Views
392
Replies
2
Views
101
Replies
5
Views
88
Replies
5
Views
110

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.

### Which adblocker are you using?

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

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