Mmult filter

[jake32008]

All,

I have the following array formula which is working well

{=MIN(MMULT(IF($G12:J61="",10^10,$G12:J61),TRANSPOSE(COLUMN($G$1:J$1))^0))}

Until I try to add a filter to it...

{=MIN(MMULT(IF(OR($F$12:$F$61>COLUMN(A1),$G12:I61=""),10^10,$G12:I61),TRANSPOSE(COLUMN($G$1:I$1))^0))}

My goal is to have the formula check two things

• that the week in question is greater than the number of weeks in column 'F' where the value represents lead time
• that there are values in column G which represent products

In other words, I only want the calculation to take into account products that are constrained by the lead time in order to truly calculate the minimum number of units we can produce.

Thank you in advance for your help!

Jake

 

[XOR LX]

My goal is to have the formula check two things

• that the week in question is greater than the number of weeks in column 'F' where the value represents lead time
• that there are values in column G which represent products

This sounds like you would want both of these conditions to be TRUE. But then your attempted formula is using an OR condition with respect to these criteria, so I'm a bit confused.

Regards

 

[jake32008]

Apologies if I didn't explain it well enough, I do want it to be an OR condition with the first check being that there is a product in column G ($G12:I61="") and the second being that if there is a product, we only want to include that product in the calculation if the week being evaluated is within the product lead time $F$12:$F$61>COLUMN(A1) where column A1 will return 1 for the current week then as I move it across columns it will adjust to 2,3,4,etc. That way when the lead time is 4 weeks we no longer consider the quantity on order as a constraint since we can always order more.

Thanks for your response!

 

[XOR LX]

Apologies if I didn't explain it well enough, I do want it to be an OR condition with the first check being that there is a product in column G ($G12:I61="") and the second being that if there is a product, we only want to include that product in the calculation if the week being evaluated is within the product lead time

But you've just described an AND condition!

Re-worded, you've basically just written that the only rows to consider are where BOTH of these are TRUE, (not EITHER):

1) There is a product in column G
2) The week being evaluated is within the product lead time

Re-read what you've just written if you don't believe me.

Regards

 

[jake32008]

I guess the confusion is coming from the fact that the ="" parameter is enough of a qualifier that I don't care what the lead time is if it's blank. You're right in that if there is a product I do also want to make sure the lead time is within the parameter set.

I am happy to try either AND or OR if you think AND will work

 

[XOR LX]

Try (array formula**):

=MIN(IF($F$12:$F$61>COLUMNS($A1:A1),IF($G12:I61<>"",MMULT(N(+$G12:I61),TRANSPOSE(COLUMN($G$1:I$1))^0))))

COLUMNS is preferable to COLUMN for generating successive integers, for reasons similar to those given here:

ROW vs ROWS for consecutive integer generation « EXCELXOR

Regards

 

[jake32008]

Thank you! When I get back to the office I'll give it a shot!

 

[jake32008]

You are awesome my friend! Thank you!!

 

[XOR LX]

You're welcome!

Cheers