# Finding interval averages

#### inonothing

##### New Member
Hello!

I currently a service member and I am trying look for ways to improve how fast my base can complete patient physicals. I have created a form function that inputs the last 4 digits of a patients social security number and the time which they checked out and the time they checked in. I end up with 4 columns of data (last 4, Time out, Last 4, Time in). I am trying to find out how long it takes for each person(indicated by the last 4) to go through my exam process. My hope is to find the average. Now that would be easy if the patients checked in and out in the same order, but they don't. How do I correctly calculate the average? Furthermore, I want it to ignore records that haven't been checked back in.

Your thoughts and Ideas are greatly appreciated!
 Last 4 Time out Last 4 Time In 1234 7:45AA 1236 8:00AM 1235 7:50AM 1234 8:15AM 1236 7:55AM

<tbody>
</tbody>

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
You need to format the cells as time

Sheet1

 A B C D E F G H 1 Last 4 Time out Last 4 Time In Time taken Average time 2 1234 7:45 AM 1236 8:00 AM 00:15:00 00:20:00 3 1235 7:50 AM 1234 8:15 AM 00:25:00 4 1236 7:55 AM

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 65px;"><col style="width: 110px;"><col style="width: 102px;"><col style="width: 115px;"><col style="width: 64px;"><col style="width: 104px;"><col style="width: 64px;"><col style="width: 115px;"></colgroup><tbody>
</tbody>

 Cell Formula F2 =IF(D2="","",D2-B2) H2 =SUM(F:F)/COUNT(F:F) F3 =IF(D3="","",D3-B3) F4 =IF(D4="","",D4-B4)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Maybe VLOOKUP.
I put the IFERROR so you don't get an "N/A" for people that have not checked out.
As stated by Dryver14 you need to formate the check in and out as times.
Excel Workbook
ABCDE
1Last 4Time InLast 4Time outTotal Time
212347:45 AM12368:00 AM0:30:00
312357:50 AM12348:15 AM
412367:55 AM0:05:00
5Average =0:17:30
Sheet

Replies
1
Views
100
Replies
1
Views
209
Replies
4
Views
132
Replies
3
Views
291
Replies
1
Views
444

1,218,700
Messages
6,143,966
Members
450,521
Latest member
Albertie

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