calculating a count of patients in and out of a bed to work out beds used?

[exceliz]

Hi

I am not sure if this could be done easily but thought i would check on here with the experts...

I have a yearly total table count by day of the week of how many patients are admitted to a bed, and another table that shows what their length of stay is on average also by day of the week - i was going to then multiple these together to work out how many bed days this equates to, then divided by how many days in the year to figure how many beds are used per day on average.

however its kinda flawed. i.e. if there are 170 patients with an avg length of stay of 2.2 days on a tuesday this equates to 7 beds but i cannot allocate all these beds to a tuesday, if they only stay for 2.2 days they would use beds on a tuedsay and wednesday... how would i spread the allocation over the days? taking into account on monday patients stay in for 4 days ...

eg - my tables
count
mon tue wed thu fri sat sun
97 170 111 71 101 14 4

Length of stay
mon tue wed thu fri sat sun
2.2 2.2 1.9 1.7 2.9 1.7 7.3

beds equates to (count * length of stay / days in year)
mon tue wed thu fri sat sun
4.1 7 4.0 2.3 5.6 0.5 0.6

any advice on how to approach this will gratefully be recieved?
thanks
liz x