Allocating tasks by site, staff number and current outstanding work

[flyte]

Good Day All,

I need some help in getting a formula that will Run through a list and then allocate a caseload in proportion to how many staff are at that site.

I have the data set out as follows: (the number for new cases sits in B2 1074)

Column A Column B Column C Column D
Zone Staff Numbers Current Case Allocation New Case Allocation
Central NSW 7 905
Central QLD 3 144
Eastern Vic 2 225
North NSW 3 332
South QLD 6 1116
West Vic 6 600

and so the listing continues - I would like a formula that looks at staff numbers and current cases and then distributes new cases as close to fairly as possible - the result would be in Column D.

Any help would be greatly appreciated.

Cheers

Natasha

 

[NARAYANK991]

Hi ,

Can you explain with a manual working of the sample data ?

Given the data you have posted , how would the figure of 1074 be distributed ?

Narayan

 

[flyte]

Hi ,

Can you explain with a manual working of the sample data ?

Given the data you have posted , how would the figure of 1074 be distributed ?

Narayan

Hi Narayan,

Sorry - yes that is what I am after - how would the figure 1074 be distributed against my listings.

Thanks for pointing that out.

 

[NARAYANK991]

Hi ,

How can anyone develop a formula to do something which cannot be done manually ?

First , there should be a method by which you can do what ever is to be done manually. A formula can then implement this algorithm ; if the algorithm itself is not known , then what formula can be given ?

What is the meaning of the following :

distributes new cases as close to fairly as possible

There is one region which is handling 225 cases with 2 people ; there is another which is handling 144 cases with 3 people. What will fair distribution do ? Is it supposed to look at the number of cases per person ? Is there any upper limit for this ?

Narayan