Average number of days in facility

K

Kidmon

Board Regular
Joined
Mar 4, 2011
Messages
71
I am using excel 2007.

I work in a jail. I need to calculate the AVERAGE number of days an inmate is in our facility. The problem is that inmates come and go and come back again.
Here's an example:
Inmate A: IN 1/1/11
OUT 1/31/11 Total days=30 days

Inmate B: IN 1/1/11
OUT 1/11/11
Back IN 1/20/11
Back OUT 1/30/11 Total Days=20 days

Inmate C: IN 2/10/11
OUT - NO, HE IS STILL HERE.

My Question: How can I figure an AVERAGE No. Of DAYS in Jail, within specific period/week?

Now my excel sheet now has this format:
Column B is: DATE IN
Column C is: DATE OUT
Column D is: TOTAL no. of Days in Facility.
How can I make this find an AVERAGE NUMBER OF DAYS in jail?

Thanx for your help, This forum is wonderful.
 
Last edited:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">Inmate </td><td style=";">In</td><td style=";">Out</td><td style=";">days</td><td style=";">Average</td><td style=";">Excel 07/10</td><td style=";">Excel <07/10</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">A</td><td style="text-align: right;;">1/1/2011</td><td style="text-align: right;;">1/4/2012</td><td style="text-align: right;;">368</td><td style=";">A</td><td style="text-align: right;;">369.3333</td><td style="text-align: right;;">369.3333333</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">B</td><td style="text-align: right;;">1/2/2011</td><td style="text-align: right;;">1/5/2012</td><td style="text-align: right;;">368</td><td style=";">B</td><td style="text-align: right;;">369.3333</td><td style="text-align: right;;">369.3333333</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">C</td><td style="text-align: right;;">1/3/2011</td><td style="text-align: right;;">1/6/2012</td><td style="text-align: right;;">368</td><td style=";">C</td><td style="text-align: right;;">370</td><td style="text-align: right;;">370</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">D</td><td style="text-align: right;;">1/4/2011</td><td style="text-align: right;;">1/9/2012</td><td style="text-align: right;;">370</td><td style=";">D</td><td style="text-align: right;;">371</td><td style="text-align: right;;">371</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">A</td><td style="text-align: right;;">1/5/2011</td><td style="text-align: right;;">1/10/2012</td><td style="text-align: right;;">370</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style=";">B</td><td style="text-align: right;;">1/6/2011</td><td style="text-align: right;;">1/11/2012</td><td style="text-align: right;;">370</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style=";">A</td><td style="text-align: right;;">1/7/2011</td><td style="text-align: right;;">1/12/2012</td><td style="text-align: right;;">370</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style=";">B</td><td style="text-align: right;;">1/8/2011</td><td style="text-align: right;;">1/13/2012</td><td style="text-align: right;;">370</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style=";">C</td><td style="text-align: right;;">1/9/2011</td><td style="text-align: right;;">1/16/2012</td><td style="text-align: right;;">372</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style=";">D</td><td style="text-align: right;;">1/10/2011</td><td style="text-align: right;;">1/17/2012</td><td style="text-align: right;;">372</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table>
Sheet1​


<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">F2</th><td style="text-align:left">=AVERAGEIF($A$2:$A$11,E2,$D$2:$D$11)</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">D2</th><td style="text-align:left">=C2-B2</td></tr></tbody></table></td></tr></tbody></table>
<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Array Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">G2</th><td style="text-align:left">{=AVERAGE(IF($A$2:$A$11=E2,$D$2:$D$11,""))}</td></tr></tbody></table>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself</td></tr></tbody></table>
Drag formulas down.
 
Upvote 0
Thank you, I like you solution, it is better than what I had. But I need to take it a couple of steps further. I also need to break it down to a WEEKLY AVERAGE within a given date range. And I need to summarize this info from Sheet 1 on my summary page contained in SHEET 2.
Thanx again for your help.

Here is an example of sheet 1 and sheet 2

Sheet 1 is:
Excel Workbook
ABCD
1Inmate Name or ActivityDate INDate OUTTotal days in pod
37Inmate A2/8/1102/23/1115
38Inmate B2/2/1104/11/1168
100Inmate C1/31/1104/15/1174
101Inmate D1/1/1103/14/1172
102Inmate A returns2/25/1103/03/116
2N Stats
Excel 2010
Cell Formulas
RangeFormula
D37=IF(B37,C37-B37,"")
D38=IF(B38,C38-B38,"")
D100=IF(B100,C100-B100,"")
D101=IF(B101,C101-B101,"")
D102=IF(B102,C102-B102,"")


Sheet 2 is:
Excel Workbook
ABCDEF
12011JAN 1/1/11 - 1/11JAN 1/12 - 1/18JAN 1/19 - 1/25JAN 1/26 - 2/1FEB 2/2 - 2/8
2Weekly Pod COUNT143151160158158
3Average Daily 2N Count2022232323
4Average 2N Stay64
JAN 1 - APR 5, 2011
Excel 2010
Cell Formulas
RangeFormula
B2=SUMIFS('2N Stats'!$J:$J,'2N Stats'!$G:$G,">="&"1/1/2011"+0,'2N Stats'!$G:$G,"<="&"1/11/2011"+0)
B3=AVERAGEIFS('2N Stats'!$J:$J,'2N Stats'!$G:$G,">=1/1/2011",'2N Stats'!$G:$G,"<=1/11/2011")
B4=IFERROR(AVERAGEIFS('2N Stats'!$D:$D,'2N Stats'!$G:$G,">=1/1/2011",'2N Stats'!$G:$G,"<=1/11/2011"),"")
C2=SUMIFS('2N Stats'!$J:$J,'2N Stats'!$G:$G,">="&"1/12/2011"+0,'2N Stats'!$G:$G,"<="&"1/18/2011"+0)
C3=AVERAGEIFS('2N Stats'!$J:$J,'2N Stats'!$G:$G,">=1/12/2011",'2N Stats'!$G:$G,"<=1/18/2011")
C4=IFERROR(AVERAGEIFS('2N Stats'!$D:$D,'2N Stats'!$G:$G,">=1/12/2011",'2N Stats'!$G:$G,"<=1/18/2011"),"")
D2=SUMIFS('2N Stats'!$J:$J,'2N Stats'!$G:$G,">="&"1/19/2011"+0,'2N Stats'!$G:$G,"<="&"1/25/2011"+0)
D3=AVERAGEIFS('2N Stats'!$J:$J,'2N Stats'!$G:$G,">=1/19/2011",'2N Stats'!$G:$G,"<=1/25/2011")
D4=IFERROR(AVERAGEIFS('2N Stats'!$D:$D,'2N Stats'!$G:$G,">=1/19/2011",'2N Stats'!$G:$G,"<=1/25/2011"),"")
E2=SUMIFS('2N Stats'!$J:$J,'2N Stats'!$G:$G,">="&"1/26/2011"+0,'2N Stats'!$G:$G,"<="&"2/1/2011"+0)
E3=AVERAGEIFS('2N Stats'!$J:$J,'2N Stats'!$G:$G,">=1/26/2011",'2N Stats'!$G:$G,"<=2/1/2011")
E4=IFERROR(AVERAGEIFS('2N Stats'!$D:$D,'2N Stats'!$G:$G,">=1/26/2011",'2N Stats'!$G:$G,"<=2/1/2011"),"")
F2=SUMIFS('2N Stats'!$J:$J,'2N Stats'!$G:$G,">="&"2/2/2011"+0,'2N Stats'!$G:$G,"<="&"2/8/2011"+0)
F3=AVERAGEIFS('2N Stats'!$J:$J,'2N Stats'!$G:$G,">=2/2/2011",'2N Stats'!$G:$G,"<=2/8/2011")
F4=IFERROR(AVERAGEIFS('2N Stats'!$D:$D,'2N Stats'!$G:$G,">=2/2/2011",'2N Stats'!$G:$G,"<=2/8/2011"),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Averaging the difference between two dates and separating them into categories
Replies
5
Views
303
awoohaw
awoohaw
B
  • Question Question
Chart help - rainfall data
Replies
2
Views
206
JV0710
J
J
  • Question Question
Using CountIf to find total number based on 2 different cells?
Replies
1
Views
126
Asbestos_Jen
Asbestos_Jen
B
  • Solved
Average of last X transactions by customer
Replies
5
Views
311
Dave Patton
D
S
  • Solved
Way to move values based on another cell around like chess pieces
Replies
4
Views
307
myall_blues
myall_blues

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

Share this page

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
Back
Top