Finding interval averages

[inonothing]

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>

 

[Dryver14]

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>

Spreadsheet Formulas

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

 

[AhoyNC]

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
	A	B	C	D	E
1	Last 4	Time In	Last 4	Time out	Total Time
2	1234	7:45 AM	1236	8:00 AM	0:30:00
3	1235	7:50 AM	1234	8:15 AM
4	1236	7:55 AM			0:05:00
5				Average =	0:17:30
Sheet