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.
Last edited by XOR LX; Mar 15th, 2016 at 11:55 AM.
Advanced Excel Techniques: http://excelxor.com/
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!
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/
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.
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/
Thank you! When I get back to the office I'll give it a shot!
You are awesome my friend! Thank you!!
You're welcome!
Cheers
Advanced Excel Techniques: http://excelxor.com/
Like this thread? Share it with others