Personnel Sizing

[Thomas Kuriakose]

Respected Sir,

We need to calculate number of personnel in departments based on number of projects with reference to the roles given fixed workload.

Kindly find attached a data on which the values are computed based on number of projects = 4000.

The personnel sizing D5:D23 = Number of Projects (C2)/Workload of specific role (C5:C22)

If the sum of personnel sizing works out to 2 for a specific role, the distribution of personnel for position and role should follow position hierarchy, for example - if C2 is 8000, then for Role 2, there are two personnel, one for EDM and the other for PM.

Kindy guide on how the values in F5 to F23 can be updated based on number of projects in C2.

Thank you very much,

with regards,
thomas

 

[bosco_yip]

Maybe...

1] E5, copied down :

=IF(C5="","",MAX(1,FLOOR(MAX(1,FLOOR(SUMIF(A$5:INDEX(B5:B$50,MATCH("Total*",A5:A$50,0)),A5,D$5),1)),1)))

2] F5, copied down :

=IF(LEFT(A5,5)="Total",SUM(F$4:F4)-SUMIF(A$4:A4,"Total*",F$4:F4)*2,IF(COUNTIF(A$5:A5,A5)>E5,0,IF(COUNTIF(A$5:A$50,A5)>1,1,E5)))

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this solution provided.

Much appreciated.

I will check and get back to you.

Thank you very much once again.

with regards,
thomas

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this solution. This is working for project numbers that are greater than 3000 correctly.

Much appreciated.

If the project numbers are less than 3000, for example 100, the sum of personnel sizing is less than 1 for role 2 and the required result (F6:F10) should be zero.

kindly guide on this.

Thank you very much once again,

with regards,
thomas

 

[bosco_yip]

..........
If the project numbers are less than 3000, for example 100, the sum of personnel sizing is less than 1 for role 2 and the required result (F6:F10) should be zero.
........

1] The above picture is extracted from your post #.1.

2] Please explain your statement in post #.4 : "the sum of personnel sizing is less than 1 for role 2 and the required result (......) should be zero."

and, herein the picture example in :

D11 =0.4 => required result, F11: 1

D16 =0.8 => required result, F16: 1

the above "personnel sizing" is also less than 1, but required result =>1 NOT 0.

Regards
Bosco