Thread: Mmult filter Thanks: 0 Likes: 0

1. Mmult filter

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

2. Re: Mmult filter

Originally Posted by jake32008
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

3. Re: Mmult filter

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!

4. Re: Mmult filter

Originally Posted by 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
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

5. Re: Mmult filter

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

6. Re: Mmult filter

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

7. Re: Mmult filter

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

8. Re: Mmult filter

You are awesome my friend! Thank you!!

9. Re: Mmult filter

You're welcome!

Cheers

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•