Help with Creating a Schedule

[dinokathy]

Morning all,



I need some help. I need to create a schedule and cannot figure out how to do it properly. Here's what I need to:



Take the value of a cell (in the link below, that's cells B4 and E4) and divide it by the number of hours in the schedule (B1 and E1) and then create a number that will sit in the schedule at 15 minute intervals. During hour 3 and hour 4, consistent with each schedule, I need to reduce the number of slots by 25%.



The problem I am having is that that fractions add up to the total but due to rounding, either add too many or too little when you add up the rounded number and I cannot figure out how to 'smooth' them out and add to another cell. I've tried alternating the rounding by having every other cell round up but that's not helping! If I could get the numbers close consistently, that would be great. They don't have to match right on the nose but within one or two would be acceptable. The customary range of the number in cells B4 and E4 is between usually between 25 and 200 and the number of hours is 4, 5 or 6 hours.



https://drive.google.com/file/<wbr>d/1EqVp_<wbr>dycVMYrX8TIkNWMuTgKEN8ogvQw/<wbr>view?usp=sharing



ANY help is really really appreciated. Thank you

 

[dinokathy]

*Just realised I had no row 31, change C to $B$36 or remove the row

Wow. That's freaking awesome and wow. Thank you so much! Wow. Thank you thank you!

I have another project swimming in my head related to this one that the logic you have shown me might help me unlock and it is related to this project. It has to do with the donor flow but think about this for a second and let me know if you have a way to work it out. I had it about 1/2 done but never was able to finish it out:

If you've ever given blood, you know you walk in, read some information, go behind the screen, answer some questions, we take your vitals and then you go out and sit in the beds and give blood. The whole process, if there's no wait, should take about 45 minutes or so. Some a little longer due to having to look up medications you are taking, areas you have traveled, your blood is flowing slowly because of small veins, etc. I am trying to figure a way to estimate, given the number of staff at a blood drive, which relates directly to capacity, and the presenting number of donors. Another variable in the process is a deferral - someone who comes in to give and spends time reading and going behind the screen to answer questions or have vitals checked but is turned away due to high blood pressure, tattoos, etc. Those add to the process on the front end but not on the back end (the actual needle in the arm part). The timing of the variables for the donor are as follows:

Reading - 2-3 min
Screening - 5-15 min, average is about 8
Actual donation - 15-30 (6-10 or so of that is needle in the arm time, rest is staff preparing the blood bag, scrubbing your arm, sitting in the chair after your give recovering)

So what I am trying to do is create a sheet where you input the following variables:
# of staff
Donors processed per staff per hour
# hours
# donors needing to be seen
# health history stations
# donor beds

Then, based on those variables, take the donor processing times from above and randomize the amount of time for each 'station' the donor visits and then move them on to the next station. As the donor flow exceeds the capacity, the time at each station will increase. So if there's a back up in Screening, the time the donor spends in Reading will increase.

Just a simple little project really....

Thank you again so much for your help. You have really helped a number of people save quite a bit of time and frustration!!!!

 

[dinokathy]

*Just realised I had no row 31, change C to $B$36 or remove the row

Got it! Thank you so much!!! Here's a link to the final product. I cannot stress how much I really appreciate the help!

https://drive.google.com/file/d/1GT14fwH-PotPeeNUfXbpifs2taNi6lBF/view?usp=sharing

 

[mrshl9898]

Wow. That's freaking awesome and wow. Thank you so much! Wow. Thank you thank you!

I have another project swimming in my head related to this one that the logic you have shown me might help me unlock and it is related to this project. It has to do with the donor flow but think about this for a second and let me know if you have a way to work it out. I had it about 1/2 done but never was able to finish it out:

If you've ever given blood, you know you walk in, read some information, go behind the screen, answer some questions, we take your vitals and then you go out and sit in the beds and give blood. The whole process, if there's no wait, should take about 45 minutes or so. Some a little longer due to having to look up medications you are taking, areas you have traveled, your blood is flowing slowly because of small veins, etc. I am trying to figure a way to estimate, given the number of staff at a blood drive, which relates directly to capacity, and the presenting number of donors. Another variable in the process is a deferral - someone who comes in to give and spends time reading and going behind the screen to answer questions or have vitals checked but is turned away due to high blood pressure, tattoos, etc. Those add to the process on the front end but not on the back end (the actual needle in the arm part). The timing of the variables for the donor are as follows:

Reading - 2-3 min
Screening - 5-15 min, average is about 8
Actual donation - 15-30 (6-10 or so of that is needle in the arm time, rest is staff preparing the blood bag, scrubbing your arm, sitting in the chair after your give recovering)

So what I am trying to do is create a sheet where you input the following variables:
# of staff
Donors processed per staff per hour
# hours
# donors needing to be seen
# health history stations
# donor beds

Then, based on those variables, take the donor processing times from above and randomize the amount of time for each 'station' the donor visits and then move them on to the next station. As the donor flow exceeds the capacity, the time at each station will increase. So if there's a back up in Screening, the time the donor spends in Reading will increase.

Just a simple little project really....

Thank you again so much for your help. You have really helped a number of people save quite a bit of time and frustration!!!!

Sounds like there's alot to think about there, maybe map out what you would want it to look like and what you want to accomplish (like where to send people in order to process as many at a time for instance), and start up a new post.