Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Average length of stay in Excel

Status
Not open for further replies.

dlbriere

Instructor
Oct 27, 2002
3
US
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top