Comparing required and available capacity with usage - need formula help

[Kimber]

I need to create a sheet/table showing capacity for machines on first and second shifts and I need to compare usage to available capacity in both whole numbers and percentages. We will always fill first shift to capacity (100%) before producing on second shift. I need formulas for:

Capacity used on 1st shift: production units required up to shift capacity
Capacity used on 2nd shift: remaining production units required left over from 1st (over 100%) if needed, otherwise, 0.
% of Capacity used on 1st shift: % of needed / total capacity up to 100%
% of Capacity used on 2nd shift: remaining % left over from 1st (over 100%) if needed, otherwise, 0.

I have data for forecast total (required) and capacities for both shifts.

Thank you.

 

[Abhijeet R. Joshi]

I does not sound quite clear to me, probably an example should suffice..

 

[Luke M]

How's this for a start?

 

[Kimber]

Simple table below showing results, but I need formulas. Additionally, 1st shift capacity used and % capacity used cannot exceed machine capacity 1st shift and 100%.


Production Units Required 150
Machine capacity - 1st shift 100
Machine capacity - 2nd shift 100
Capacity used - 1st shift 100
Capacity used - 2nd shift 50
% Capacity used - 1st shift 100%
% Capacity used - 2nd shift 50%
Additional capacity required 0

I hope this information is clear.
Thank you.

 

[Kimber]

How's this for a start?

Luke,
That is great, but my table is transposed -- your columns are my rows. Would you mind editing for me? I'm in a time crunch (as I assume most folks are) and I think this will work perfectly if transposed. Thank you so much for your prompt response.

 

[Luke M]

Easy enough.

 

[Kimber]

Easy enough.

Thank you so much, Luke. You're the best!

 

[Kimber]

Easy enough.

I've been assigned an addition to the formula. Since we don't schedule the machine for more than 80% of capacity, that needs to be included in the formula as well. Again, this is time-sensitive. I appreciate so much having such a great resource of amazing Excel gurus.

 

[Luke M]

We'll modify the formulas then in B4 and B5 to only use 80% of the given value.

 

[Kimber]

We'll modify the formulas then in B4 and B5 to only use 80% of the given value.

Thanks again, Luke, for the simple solution.