Finding interval averages

inonothing

New Member
Joined
Mar 28, 2013
Messages
1
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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
You need to format the cells as time


Sheet1

ABCDEFGH
1Last 4Time outLast 4Time In Time taken Average time
212347:45 AM12368:00 AM 00:15:00 00:20:00
312357:50 AM12348:15 AM 00:25:00
412367: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
CellFormula
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

Well-known Member
Joined
Oct 10, 2011
Messages
4,785
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,539
Messages
5,659,392
Members
418,500
Latest member
Guru Prasad S

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