Spread the number of employees proportionately across a range of cells depending on values in another cell

[ElBB23]

Hi guys,

I am looking for ideas from the wealth of knowledge within the forum

- I will try to explain as best I can but I apologise in advance if I haven't explained it well.

So, I have a worksheet with three columns (there can be more if needed) the first column shows the time period, split into 15 minute interval segments going top to bottom. The middle column is currently blank with the total amount of hours that need to be allocated at the bottom (in this case 12.5 hours), the third column shows me how many people I have available during each interval.

I need a formula, function or even VBA in the second column that will look at the number of people I have available within the third column (both at row and total level) and distribute how many people I will allocate at each time interval in a weighted fashion so that the sum of all the intervals in the second column (divided by 4 to convert it into hours) matches the number of hours required at the bottom. So for example I want the number of people allocated at 00:00 to be higher than it is at 01:00 and If possible I only want it to allocate agents if the staffing is over 5.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Time
[/TD]
[TD]Allocated
[/TD]
[TD]Available
[/TD]
[/TR]
[TR]
[TD]00:00
[/TD]
[TD][/TD]
[TD]25
[/TD]
[/TR]
[TR]
[TD]00:15
[/TD]
[TD][/TD]
[TD]24
[/TD]
[/TR]
[TR]
[TD]00:30
[/TD]
[TD][/TD]
[TD]24
[/TD]
[/TR]
[TR]
[TD]00:45
[/TD]
[TD][/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]01:00
[/TD]
[TD][/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]01:15
[/TD]
[TD][/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]01:30
[/TD]
[TD][/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]01:45
[/TD]
[TD][/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]02:00
[/TD]
[TD][/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]02:15
[/TD]
[TD][/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]02:30
[/TD]
[TD][/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]02:45
[/TD]
[TD][/TD]
[TD]-2
[/TD]
[/TR]
[TR]
[TD]03:00
[/TD]
[TD][/TD]
[TD]-2
[/TD]
[/TR]
[TR]
[TD]03:15
[/TD]
[TD][/TD]
[TD]-1
[/TD]
[/TR]
[TR]
[TD]03:30
[/TD]
[TD][/TD]
[TD]-1
[/TD]
[/TR]
[TR]
[TD]03:45
[/TD]
[TD][/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]04:00
[/TD]
[TD][/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]04:15
[/TD]
[TD][/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]Required
[/TD]
[TD]12.5 (hours)
[/TD]
[TD]46.25 hours available
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

Tricky. This might get you some of the way:

Book1
	A	B	C
1	Time	Allocated	Available
2	00:00	7	25
3	00:15	6	24
4	00:30	6	24
5	00:45	6	22
6	01:00	2	7
7	01:15	2	7
8	01:30	2	7
9	01:45	3	12
10	02:00	3	12
11	02:15	3	12
12	02:30	3	12
13	02:45	0	-2
14	03:00	0	-2
15	03:15	0	-1
16	03:30	0	-1
17	03:45	2	7
18	04:00	2	7
19	04:15	3	7
20	Required	12.5	46.25
Sheet3

Cell Formulas
Range	Formula
B2	=IF($C2>0,ROUND(C2/($C$20/$B$20),0),0)
B19	=$B$20*4-SUM($B$2:$B$18)
C20	=SUMIF(C2:C19,">0")/4

WBD

 

[ElBB23]

Hi,

I am having a slight issue with it showing minus on the last cell if the required is quite low but if I remove the round aspect of the formula it works perfectly (I know this will give fractional people but I'm ok with that if needs be for now).

 

[ElBB23]

Sorry, just another one - I don't want it to allocate any staff in an interval if the available is lower than a certain number. Its easy to insert an if formula against the available staff but its causing issues on the last row as if the staffing is under the required amount it is defaulting to zero and not giving me enough hours for the day (if that makes sense?)

 

[wideboydixon]

OK. If you're happy with fractional people then you could do this:

Book1
	A	B	C	D	E	F
1	Time	Allocated	Available		Minimum Staff:	8
2	00:00	8.74125874	25
3	00:15	8.39160839	24
4	00:30	8.39160839	24
5	00:45	7.69230769	22
6	01:00	0	7
7	01:15	0	7
8	01:30	0	7
9	01:45	4.1958042	12
10	02:00	4.1958042	12
11	02:15	4.1958042	12
12	02:30	4.1958042	12
13	02:45	0	-2
14	03:00	0	-2
15	03:15	0	-1
16	03:30	0	-1
17	03:45	0	7
18	04:00	0	7
19	04:15	0	7
20	Required	12.5	35.75
Sheet3

Cell Formulas
Range	Formula
B2	=IF($C2>=$F$1,C2/($C$20/$B$20),0)
C20	=SUMIF(C2:C19,">="&$F$1)/4

WBD