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]

I'd like to add that I've been working on this for a few hours now. I've searched high and low but for the life of me cannot figure out how to do what I want this to do! Thanks in advance for the help!

 

[dinokathy]

Bump. Any ideas? Thanks!

 

[mrshl9898]

Hi,

Really struggling to understand this. So you take 177.10 and divide it by 6 then 4. To get a number for each slot.

Then you take 25% off that for hours 3 and 4.

This leaves you with 91.66% of the original number.

What needs to happen next? Could you update the sheet with an ideal result perhaps?

 

[dinokathy]

Hi,

Really struggling to understand this. So you take 177.10 and divide it by 6 then 4. To get a number for each slot.

Then you take 25% off that for hours 3 and 4.

This leaves you with 91.66% of the original number.

What needs to happen next? Could you update the sheet with an ideal result perhaps?

Updated with an ideal schedule - https://drive.google.com/file/d/1HVogekB-usjTAf9CKvJmr6kaNRTHWHQ_/view?usp=sharing

This is a donor flow schedule for the American Red Cross for use at a blood drive. So what I need to do is try to have an even appointment schedule with slots every 15 minutes. When I just divide the total slots by the total hours and then by 4 for every 15 minutes, I get results like 5.3333 donors every 15 minutes. Since we cannot see 1/3 of a donor, that 1/3 needs to get added to the next slot. Once there's enough of the fractions to make a whole donor, then I'd like that count to go to 6, as a whole number and then have the fraction of what is left from that to add to the next slot and so on and so forth.

To further complicate matters, I need to reduce the total slots in hours 3 and 4 by 25% for each 15 minute block so the staff can have a break time built in, but I need to keep the total number of donors we see matching what is in Total Slots cell.

Thank you so much for the help!!

 

[dinokathy]

https://docs.google.com/spreadsheets/d/1rStPj6PKQtTUDtAq5tBL0QoBuVK3yuEXDtYnVD0TB_A/edit?usp=sharing

I had the wrong link. Sorry

 

[dinokathy]

Hi,

Really struggling to understand this. So you take 177.10 and divide it by 6 then 4. To get a number for each slot.

Then you take 25% off that for hours 3 and 4.

This leaves you with 91.66% of the original number.

What needs to happen next? Could you update the sheet with an ideal result perhaps?

The yellow Capacity cell will change based on the blood drive. Sometimes Capacity will be 30, sometimes it will be 144, it just depends on the blood drive. The capacity cell drives the total slots cell, which is increased sometimes by a factor of 1.15 or 1.40 (cell E2) depending on the type of blood drive it is.

 

[dinokathy]

Hi,

Really struggling to understand this. So you take 177.10 and divide it by 6 then 4. To get a number for each slot.

Then you take 25% off that for hours 3 and 4.

This leaves you with 91.66% of the original number.

What needs to happen next? Could you update the sheet with an ideal result perhaps?

And sometimes the drives are 4 hours long, sometimes they are 5 and sometimes they are 6. It's a challenge! I've been trying to figure out a way all day long!

 

[mrshl9898]

I have a sort of solution, though am really struggling to think atm so can't condense it, hopefully it at least helps you in the right direction.

B4 (and all other slots in hours 1,2,5 and 6) =B$4/B$1/4
B17 (and all other slots in hours 3 and 4) =B$4/B$1/4*0.75

Then in row 7 in the following columns (copied down to row 36, all cells)

C =B7/SUM($B$7:$B$35)
D = B$4*C7
E =ROUNDDOWN(D7,0)
F =D7-E7
G =ROUNDDOWN(SUM(D$7:D7)-SUM(E$7:E7),0)
H =IF(G7-G6=1,1,0)
I =E7+H7

This leaves a total of 177. The other 0.1 will probably struggle to donate much blood with 90% of their body missing anyway

I've tested it on 4 and 6 hours

 

[mrshl9898]

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