• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index with sequential criteria

M

mq1973

New Member
Hi Guys,

I have this table of equipment vs products. the cell where a product is made on a given equipment is marked "Y". I need a formula in column FS that identifies which product that has the lowest MDD (row 12) and then the lowest batch size of the found results.

So in the attached image for equipment line 47, there are 4 products that have the highest MDD, and therefore, the product with the lowest batch size should appear in FS47.

Chandoo.jpg
 

Attachments

  • Calculations.xlsx
    87 KB · Views: 14
Hi ,

I am confused.

In the first paragraph , you say :
I need a formula in column FS that identifies which product that has the lowest MDD (row 12) and then the lowest batch size of the found results.
Click to expand...
In the second paragraph , you say :
there are 4 products that have the highest MDD, and therefore, the product with the lowest batch size should appear in FS47.
Click to expand...
The workbook you have uploaded shows 2 products which have an MDD of 1600 in cells AU12 and AV12.

Can you clarify ?

Narayan
 
Narayan, I know what would be the output in FS13:FS15 and that should be Glymet 850 mg F/C Tablets because the it has the smallest batch size.

Both Glymet 850 mg F/C Tablets and Glymet 500 mg F/C Tablets have the highest MDD but the formula should select Glymet 850 mg F/C Tablets since is has a smaller batch size. I hope that helps.

Thanks you Narayan
product B.jpg
 
Narayan, I also need to modify the formula in FR13
IF(COUNTIF(E13:FQ13,"Y"),INDEX($E$4:$FQ$4,MATCH(SUMPRODUCT(MAX(($E13:$FQ13="Y")*($E$67:$FQ$67+$E$10:$FQ$10/100000))),INDEX(($E13:$FQ13="Y")*($E$67:$FQ$67+$E$10:$FQ$10/100000),0),0)),"")
This formula is looking for the highest risk score (row 67) and highest strength (row 10) but I need to identify the product with highest risk score and lowest ADE (row 12).
 
guys, anyone call help deciphering this formula?

IF(COUNTIF(E13:FQ13,"Y"),INDEX($E$4:$FQ$4,MATCH(SUMPRODUCT(MAX(($E13:$FQ13="Y")*($E$67:$FQ$67+$E$10:$FQ$10/100000))),INDEX(($E13:$FQ13="Y")*($E$67:$FQ$67+$E$10:$FQ$10/100000),0),0)),"")
This formula is looking for the highest risk score (row 67) and highest strength (row 10) but I need to identify the product with highest risk score and lowest ADE (row 12).
 
Hi mq1973,

Call me crazy, but I believe I was confronted to a similar problem where a double rank criteria had to applied. The solution we found was sorting the data first. Ascending and descending. Then the first match following the search criteria would be the correct one.
Now in your data the sorting must happen on the columns. (I deleted your second row, but it can work if you unmerge the cells).
Row 11 being MDD (and thus highest) and row 7 being kg (so smallest batch size)
upload_2018-1-5_15-10-21.png

A little tweak of your formula:
=IF(COUNTIF(E12:FQ12,"Y"),INDEX($E$3:$FQ$3,MATCH(SUMPRODUCT(MAX(($E12:$FQ12="Y")*($E$11:$FQ$11))),INDEX(($E12:$FQ12="Y")*($E$11:$FQ$11),0),0)),"") gave me Glymet 850 mg F/C Tablets as result.

Please check out if this is acceptable for you. I could not figger out a formula where a combination of MIN and MAX worked correctly. Though I believe this is possible.

Cheers
G.
 

Attachments

  • Calculations.xlsx
    86.9 KB · Views: 3
GraH - Guido said:
Hi mq1973,

Call me crazy, but I believe I was confronted to a similar problem where a double rank criteria had to applied. The solution we found was sorting the data first. Ascending and descending. Then the first match following the search criteria would be the correct one.
Now in your data the sorting must happen on the columns. (I deleted your second row, but it can work if you unmerge the cells).
Row 11 being MDD (and thus highest) and row 7 being kg (so smallest batch size)
View attachment 48610

A little tweak of your formula:
=IF(COUNTIF(E12:FQ12,"Y"),INDEX($E$3:$FQ$3,MATCH(SUMPRODUCT(MAX(($E12:$FQ12="Y")*($E$11:$FQ$11))),INDEX(($E12:$FQ12="Y")*($E$11:$FQ$11),0),0)),"") gave me Glymet 850 mg F/C Tablets as result.

Please check out if this is acceptable for you. I could not figger out a formula where a combination of MIN and MAX worked correctly. Though I believe this is possible.

Cheers
G.
Click to expand...
Thanks G,

I really appreciate your input. Sorting may not be a good option for me because I need to divide the products into groups (row 2).

Can you please help understand your formula
IF(COUNTIF(E12:FQ12,"Y"),INDEX($E$3:$FQ$3,MATCH(SUMPRODUCT(MAX(($E12:$FQ12="Y")*($E$11:$FQ$11))),INDEX(($E12:$FQ12="Y")*($E$11:$FQ$11),0),0)),""). What is it looking for?

I know there must be a formula for combination of min and max criteria
 
mq1973 said:
guys, anyone call help deciphering this formula?

IF(COUNTIF(E13:FQ13,"Y"),INDEX($E$4:$FQ$4,MATCH(SUMPRODUCT(MAX(($E13:$FQ13="Y")*($E$67:$FQ$67+$E$10:$FQ$10/100000))),INDEX(($E13:$FQ13="Y")*($E$67:$FQ$67+$E$10:$FQ$10/100000),0),0)),"")
This formula is looking for the highest risk score (row 67) and highest strength (row 10) but I need to identify the product with highest risk score and lowest ADE (row 12).
Click to expand...
mq1973, can you try this:
IF(COUNTIF(E13:FQ13,"Y"),INDEX($E$4:$FQ$4,MATCH(SUMPRODUCT(MIN(($E13:$FQ13="Y")*($E$67:$FQ$67+$E$12:$FQ$12/100000))),INDEX(($E13:$FQ13="Y")*($E$67:$FQ$67+$E$12:$FQ$12/100000),0),0)),"")

What this does in making a sum of the ranges risk in E67:FQ67 and ADE E12:FQ12/100000 to make very small differences in the range you want to evaluate, but still big enough to enable the correct ranking.
Since you request the min ADE, MIN is used in the SUMPRODUCT. SUMPRODUCT will perform the array SUM. This position of the MIN value is found via MATCH in the index range of you product names.
In an effort to find the a solution for your other formula, I stumbled across a very good explanation of this (By Mike Girvin) on the Mr Excel forum, but I cannot find the reference back, sorry for that. But try to google it.
To decipher formulas you can always evaluate the formula as well.
upload_2018-1-6_11-52-17.png
 
Hello again mq1973,

Try this for your second request.
=IF(COUNTIF(E13:FQ13,"Y"),INDEX($E$4:$FQ$4,,MATCH(AGGREGATE(15,6,($E$8:$FQ$8)/((E13:FQ13="Y")*($E$12:$FQ$12)=MAX((E13:FQ13="Y")*($E$12:$FQ$12))),1),($E$8:$FQ$8)/((E13:FQ13="Y")*($E$12:$FQ$12)=MAX((E13:FQ13="Y")*($E$12:$FQ$12))),0)),"")
To confirm with CONTROL + SHIFT + ENTER.

Cell B19 contains " Y" and not "Y", you might need to correct this.

G.
 

Attachments

  • Calculations.xlsx
    90 KB · Views: 3
GraH - Guido said:
Hello again mq1973,

Try this for your second request.
=IF(COUNTIF(E13:FQ13,"Y"),INDEX($E$4:$FQ$4,,MATCH(AGGREGATE(15,6,($E$8:$FQ$8)/((E13:FQ13="Y")*($E$12:$FQ$12)=MAX((E13:FQ13="Y")*($E$12:$FQ$12))),1),($E$8:$FQ$8)/((E13:FQ13="Y")*($E$12:$FQ$12)=MAX((E13:FQ13="Y")*($E$12:$FQ$12))),0)),"")
To confirm with CONTROL + SHIFT + ENTER.

Cell B19 contains " Y" and not "Y", you might need to correct this.

G.
Click to expand...
In fact, I'm just plainly stupid. This little tweak of the original formulas seems to return the same result, and it is faster to calculate.
=IF(COUNTIF(E13:FQ13;"Y"),INDEX($E$4:$FQ$4;MATCH(SUMPRODUCT(MAX(($E13:$FQ13="Y")*($E$12:$FQ$12-$E$8:$FQ$8/100000))),INDEX(($E13:$FQ13="Y")*($E$12:$FQ$12-$E$8:$FQ$8/100000),0),0)),"")

It searches for the max MDD that is corrected with the weight/100000. In your example above: 1600 will become 1599,9996673 and 1599,996560. Returning the max is where MDD is highest but weight is lowest.
 
You must log in or register to reply here.
Back
Top