Mmult filter

jake32008

New Member
Joined
Apr 10, 2015
Messages
27
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
 
Last edited:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Last edited:
Upvote 0
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!
 
Upvote 0
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
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top