Percentage of Days

[Robby19]

So I have another spreadsheet that computes the total manpower for specific sections and assigns a "mission" for each day. To fulfill the "mission" we have to breakdown the total manpower and assign each section a set amount of days based off of how many people they have/can support. Each day (including weekends/holidays) must be accounted for.

Is there a way for excel to automate this?

I need to evenly spread the manpower for days assigned. If excel can automate the days assigned, then I should be able to automate the percentage of days assigned as well. For May, there are 31 days, so I need to assign 31 people from the Total Manpower as fairly as possible.

**The below information does not equal 100%. This is a redacted view of the data.**

[TABLE="width: 500"]
<tbody>[TR]
[TD]Section[/TD]
[TD]Total Manpower[/TD]
[TD]Percentage of Manpower[/TD]
[TD]Percentage of Days[/TD]
[TD]Days Assigned[/TD]
[/TR]
[TR]
[TD]HQ[/TD]
[TD]29[/TD]
[TD]7.20%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]81[/TD]
[TD]20.10%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]63[/TD]
[TD]15.63%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]70[/TD]
[TD]17.37%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[iliace]

Can you post an example of what Days Assigned would look like in the sample you posted?

 

[Robby19]

My apologies, I was able to figure this out with the use of a helper column.

 

[iliace]

That's great! I had this on my to-do (to-reply) list for a week now, but didn't get around to it

Do consider posting your solution here so that others may benefit.

 

[Robby19]

As requested;

Percentage of PAX is

=[@Total]/Table1[[#Totals],[Total]]

Dividing the Total by the Total number of PAX (people)

Percentage of Days is

=Days*[@[Percentage of PAX]]

Days = # of days in the month

# of Days to be Assigned is

=ROUND([@[Percentage of Days]],0)

This column is Totalled using

=IF(SUM(['# of Days to be Assigned])>Days,"OVERMANNED ("&SUM(['# of Days to be Assigned])&")",IF(SUM(['# of Days to be Assigned])

<days,"undermanned be="" to="" Days="" of="" Assigned])&?)?,SUM([?#="" (?&SUM([?#="" Assigned])))
<days,"undermanned be="" to="" Days="" of="" Assigned])&?)?,SUM([?#="" (?&SUM([?#="" CODE]
It is looking at the total number of days in the month and then displaying Overmanned or undermanned based off of that data.

Lastly I created another column to the right, Actual # of days Assigned which will override the # of Days to be Assigned. This way you can adjust the numbers as necessary to the number of days per month.

Hope this all makes sense.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Total
[/TD]
[TD]Percentage of PAX
[/TD]
[TD]Percentage of Days
[/TD]
[TD]# of Days to be Assigned
[/TD]
[TD]Actual # of Days Assigned
[/TD]
[/TR]
[TR]
[TD]34
[/TD]
[TD]7.49%
[/TD]
[TD]2.32%
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]85
[/TD]
[TD]18.72%
[/TD]
[TD]5.8%
[/TD]
[TD]6
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]55
[/TD]
[TD]14.54%
[/TD]
[TD]4.51%
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
</days,"undermanned></days,"undermanned>

 

[Robby19]

This is driving me crazy. The Overmanned code is not being written in the above message.

Nor is it working in this one. Basically it is showing OVERMANNED (#) if the total is over the number of days in the month and UNDERMANNED (#) if the total is under.