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

#### exceliz

##### Board Regular
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

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Replies
3
Views
557
Replies
2
Views
622
Replies
2
Views
365
Replies
5
Views
516
Replies
8
Views
323

1,196,249
Messages
6,014,229
Members
441,808
Latest member
xplainer

### 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.

### Which adblocker are you using?

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

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