Results 1 to 9 of 9

Thread: Mmult filter
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2015
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by jake32008; Mar 15th, 2016 at 11:42 AM.

  2. #2
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Mmult filter

    Quote Originally Posted by jake32008 View Post
    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 by XOR LX; Mar 15th, 2016 at 11:55 AM.
    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    New Member
    Join Date
    Apr 2015
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Mmult filter

    Quote Originally Posted by jake32008 View Post
    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 by XOR LX; Mar 15th, 2016 at 12:37 PM.
    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    New Member
    Join Date
    Apr 2015
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by jake32008; Mar 15th, 2016 at 12:48 PM.

  6. #6
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Advanced Excel Techniques: http://excelxor.com/

  7. #7
    New Member
    Join Date
    Apr 2015
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Mmult filter

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

  8. #8
    New Member
    Join Date
    Apr 2015
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Mmult filter

    You are awesome my friend! Thank you!!

  9. #9
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Mmult filter

    You're welcome!

    Cheers
    Advanced Excel Techniques: http://excelxor.com/

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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