Formula Help

[Stoney Currie]

Hi,

I need a formula that tells me when I have exceeded the optimum amount of
production in a 24 hour period and 16.5 hour period along with an 8.5 period.
We work in batches and a batch could take up to 20 minutes. We can have up
to 5 to 10 different batches in one of the above periods, each different
batch could take a different amount of time. I do have this times on another
tab called Batch Times.
Product A could take 20 mins. and product B could take 42 mins. If I plan
to produce 15 batches of product A that should take 300 minutes or 5 hours. I
now plan to produce product B at 42 mins a batch. I want 6 batches. This
should take 252 mins. or 4.2 hours. This exceeds 510 minutes or 8.5 hours and
this is where I want to be flagged with the word FULL. If I didn't have
enough production set up I would want to be flagged with word PRODUCE. Is
this possible?
Example A
A1 A2
# of Batches
Product # 15
Product # 20
FULL

Example B
A1 A2
# of Batches
Product # A 10
Product # B 5
PRODUCE
Thanks

 

[JLatham]

This one is a little tough because we don't have information on you you
determine if the group you're looking at is a 24, 16.5 or 8.5 hour period.
But I'll try to give a general solution.

First it assumes that your [Batch Times] sheet is laid out with product IDs
in column A and the time to produce each in column B.

On your working sheet, and is easier to deal with if you add a helper column C

A B C
1 Product # of Batches Time
2 A 15 formula 1
3 B 20 formula 1
4 formula 2

The formulas would be like this:
formula 1 for cell C2
=VLookup(A2,'Batch Times'!$A$1:$B$25,2,False) * B2
this assumes your table on [Batch Times] goes from A1 to B25. Fill the
formula down for all rows with products to produce in them.

Then in A4, formula 2, you can have a formula similar to:
=IF(SUM(C2:C3)>(16.5 * 60),"FULL","PRODUCE")
Change the 16.5 to 24, 16.5 or 8.5 depending on the time period in question.

Hope this helps some.