Average length of stay in Excel

[dlbriere]

I have a report that lists the date a student arrives and the date the student departs. I need to figure not only the length of stay, but also the average length of stay for all students. How can I accomplish this in Excel? Also I need the average LOS as a whole number

Thanks

Don

 

[HobbitK]

dlbriere,
I'm not sure if this is exactly what you need or not, but try this very simple solution.

'Assumes check in date is in Row B starting with column 2
'Assumes check out date is in row C starting at column 2
'Assumes length of stay os in Row D starting at column 2

In cell D:2 use the Days360 Function. choosing B:2 as start and C:2 as ending.
Copy that function down as many rows as you need. this will provide you the number of days they stayed.

For the average.
Format the cell you want to use for that calculation as a number with NO decimal places.
Then in that cell use the average function. Highlight all cells in row D that is not blank and you want involved in your calculation. Excel will average all those calculated cells and display a whole number. I am not sure about how it will deal with numbers that should be rounded up.
Michael

 

[xlbo]

mmmm
Dunno 'bout days 360.
If you got start date in A2 and End date in B2, then in C2, enter:
=B2-A2
Format this cell as NUMBER and you will have the number of days between start and end date
To get your average, in D2, enter =mround(average(C2:C100),1)
(where your list goes up to row 100)
NB - MROUND rounds up, away from zero for .5 or above and down for <.5 Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes