How do I incorporate a formula to calculate many months releases?

[soconfused]

I am trying to level load a shop floor. I am trying to take a certain number
of hours and spread it over 2 months on up to 5 months and spreading it into
several different buckets.

For instance:

Total hours = 160. My formula needs to say something like; If the hours are
less than 100 take 10% and put in in the ship bucket and spread the other
hours 40% to the immediate following month and 50% in the next month.

 

[Sean Timmons]

May need more details on this. What determines if it's 2, 3, 4 or 5 months?

Do you have # of hours for each month available?

 

[soconfused]

Thanks for asking for more details, here they are:

I have a total # of hours and if the hours are less than 100 I want to
spread over 3 months total 10% going directly to the ship month then spread
over the other two months. IF the hours are less than 400, but greater than
101 I want to spread over 4 months, 10% going directly to the ship month,
then spread over the other 3 months and so on.

Thanks

 

[Sean Timmons]

So..

=A2*.1

will be your ship month regardless.

then, in month 1:

=if(A2<100,A2*.4,if(A2<400,A2*.3,if(A2<1000,A2*.2)))

I would think over 4 or 5 months, you'd want 20% in month 1...

month 2:

=if(A2<100,A2*.5,if(A2<400,A2*.3,if(A2<1000,A2*.2)))

month 3:

=if(A2<100,"",if(A2<400,A2*.3,if(A2<700,A2*.25,if(A2<1000,.2))))
No month 4 for the under 100 crowd. The under 700 crowd does a little more
this month.

month 4:

=if(A2<400,"",if(A2<700,A2*.25,if(A2<1000,.2)))

month 5:

=if(A2>=700,.2,"")

Notice I have the "" to show a blank cell if the month is not needed.

Make sense?