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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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

A
  • Question
Need to convert days, hours, minutes and second text into hh:mm:ss
Replies
7
Views
132
Aadya
A
J
  • Solved
Average the last bank balance of each day
Replies
5
Views
440
JamesonMH
J
C
  • Solved
Average of income for the last 6 months (rolling) excluding weekends
2
Replies
14
Views
415
felixstraube
F
R
  • Solved
Subtracting all calendar days, but end date cannot fall on a weekend or holiday. If it does, the end date shifts to the preceding work day.
Replies
8
Views
297
Romy
R
M
  • Question
Dynamic Update to Range in RANK Based on Date
Replies
0
Views
259
multifidus
M

Forum statistics

Threads
1,215,256
Messages
6,123,903
Members
449,132
Latest member
Rosie14

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