My spreadsheet has a calculation that compares material available (both in
inventory and ready to ship to a production facility) to material required
for production. The ready to ship materials are packed in containers of
varying quantites. What the following formula is supposed to do is count the
number of containers needed for each material so that the total (in inventory
and shipable) is equal to or greater than the quantity required for
production. There are 60 different materials (rows).

=IF(K37<0,IF(L37=K37*-1,COUNT(L37),IF((L37+M37)=K37*-1,COUNT(L37:M37),IF(SUM(L37:N37)=K37*-1,COUNT(L37:N37),IF(SUM(L37:O37)=K37*-1,COUNT(L37:O37),IF(SUM(L37:P37)=K37*-1,COUNT(L37:P37),COUNT(L37:AC37)))))),0)

Column K shows the quantity of material needed (and is shown as a negative
number).
Columns L-AC show the quantity of material in each container.

I can ship a limit of 24 containers at a time but, if a particular material
requires more than 7 containers, this formula shows the total number of
continers rather than the number needed.

I've looked at some of the solutions that have been offered for similar
problems but did not see one that fit what I was seeking.

Thanks,

ronnnomad

On 18 May, 14:56, ronnomad wrote:
=IF(K37<0,IF(L37=K37*-1,COUNT(L37),IF((L37+M37)=K37*-1,COUNT(L37:M37),IF(SUM(L37:N37)=K37*-1,COUNT(L37:N37),IF(SUM(L37:O37)=K37*-1,COUNT(L37:O37),IF(SUM(L37:P37)=K37*-1,COUNT(L37:P37),COUNT(L37:AC37)))))),0)

I can ship a limit of 24 containers at a time but, if a particular material
requires more than 7 containers, this formula shows the total number of
continers rather than the number needed.

Here's a non-nested alternative:

=MIN(IF(L37K37*-1,1,999),IF(SUM(L37:M37)K37*-1,2,999),IF(SUM(L37:N37)K37*-1,3,999),IF(SUM(L37:O37)K37*-1,4,999),IF(SUM(L37:P37)K37*-1,5,999),IF(SUM(L37:Q37)K37*-1,6,),........)

The MIN will find the lowest value in the sequence (ie the first time
there is enough units). If this resolves to 999, then you don't have
enough units even taking all 24 containers.

Caveat: If you don't put a negative value in K37, results will be
unexpected.

HTH

Andrew

Andrew,

I guess I needed to supply one more piece of information. The quantity of
material in columns L-AC may be less than the number in K. You suggestion
works when the total in L-AC is greater than K, but gives no response if the
quantity is less. I want to see the number of containers even if I cannot
fulfill the total need.

Ron

"loudfish" wrote:

On 18 May, 14:56, ronnomad wrote:
=IF(K37<0,IF(L37=K37*-1,COUNT(L37),IF((L37+M37)=K37*-1,COUNT(L37:M37),IF(SUM(L37:N37)=K37*-1,COUNT(L37:N37),IF(SUM(L37:O37)=K37*-1,COUNT(L37:O37),IF(SUM(L37:P37)=K37*-1,COUNT(L37:P37),COUNT(L37:AC37)))))),0)

I can ship a limit of 24 containers at a time but, if a particular material
requires more than 7 containers, this formula shows the total number of
continers rather than the number needed.

Here's a non-nested alternative:

=MIN(IF(L37K37*-1,1,999),IF(SUM(L37:M37)K37*-1,2,999),IF(SUM(L37:N37)K37*-1,3,999),IF(SUM(L37:O37)K37*-1,4,999),IF(SUM(L37:P37)K37*-1,5,999),IF(SUM(L37:Q37)K37*-1,6,),........)

The MIN will find the lowest value in the sequence (ie the first time
there is enough units). If this resolves to 999, then you don't have
enough units even taking all 24 containers.

Caveat: If you don't put a negative value in K37, results will be
unexpected.

HTH

Andrew

On 18 May, 19:34, ronnomad wrote:
I want to see the number of containers even if I cannot
fulfill the total need.

.... then change the last value to be summed from a conditional

IF(SUM(L37:AC37)K37*-1,24,999

to an absolute:

24

That way, if there are not enough, it will still return 24.

HTH

Andrew

Sorry, I guess because I know what I want, I not being clear enough. If for
the first item I need 1000 pcs but I only have 700 pieces in two containers,
the formula answer ( for that row) should be two. I rarely (if ever) send 24
containers of one item. The shipment is a combination.

Ron

"loudfish" wrote:

On 18 May, 19:34, ronnomad wrote:
I want to see the number of containers even if I cannot
fulfill the total need.

.... then change the last value to be summed from a conditional

IF(SUM(L37:AC37)K37*-1,24,999

to an absolute:

24

That way, if there are not enough, it will still return 24.

HTH

Andrew

On 18 May, 20:50, ronnomad wrote:
Sorry, I guess because I know what I want, I not being clear enough. If for
the first item I need 1000 pcs but I only have 700 pieces in two containers,
the formula answer ( for that row) should be two. I rarely (if ever) send 24
containers of one item. The shipment is a combination.


Sorry for delay. I still don't get your requirements - can you give me
a more explanatory example?

The formula, as it stands, will give you the number of containers
required (not the number of pcs) to fulfil each particular request.

e.g. 700 in container 1, 700 in container 2, requirement is for 1000
pcs, therefore answer is 2 (more explicitly, answer is 700 from
container 1 plus 300 from container 2). The formula I already gave
should return 2 in this case.

Andrew