Formula gone wrong after it was working fine

[Med4040]

Hi guys, I used this formula before and that I had to make some changes, it stopped functioning the way it supposed to

in I7, this is the formula

IF(A7="","",INDEX(C$1:H$1,MATCH(MAX(IF(C7:H7<>"",C$15:H$15-C$5:H$5/1000)),IF(C7:H7<>"",C$15:H$15-C$5:H$5/1000),0)))

The formula is supposed to index "X' and find the name of the product that has highest score (row15) and smallest LD50 (row5)

 

[DRSteele]

There are ties, so the algorithm fails.

If you could provide sample data (together with expected outcomes) that we can copy-paste, we can conduct experiments. No one wants to type all that in.

 

[Med4040]

<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>DRSteele, thanks for your response.

I converted the table to html format online. I didn't come out perfect, but it can be copied into Excel
[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Equipment Name
[/TH]
[TH]Percepta®50mg Tablets
[/TH]
[TH]Percepta®50mg Tablets
[/TH]
[TH]Phyllocontin® Continus® F.C Tablets
[/TH]
[TH]Phyllocontin® Continus® F.C Tablets
[/TH]
[TH]Pravia® CR 200mg Tablets
[/TH]
[TH]Pravia® CR 300mg Tablets
[/TH]
[TH]Worst Case Product (A)
[/TH]
[/TR]
[TR]
[TD]Batch Size
[/TD]
[TD]S
[/TD]
[TD]L
[/TD]
[TD]S
[/TD]
[TD]L
[/TD]
[TD]S&L
[/TD]
[TD]S&L
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Batch Size, kg
[/TD]
[TD]13.12
[/TD]
[TD]26.25
[/TD]
[TD]54.70
[/TD]
[TD]109.40
[/TD]
[TD]48.00
[/TD]
[TD]72.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MDD, mg
[/TD]
[TD]1200
[/TD]
[TD]1200
[/TD]
[TD]700
[/TD]
[TD]700
[/TD]
[TD]600
[/TD]
[TD]900
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LD50, mg/kg
[/TD]
[TD]1024
[/TD]
[TD]1024
[/TD]
[TD]243
[/TD]
[TD]243
[/TD]
[TD]670
[/TD]
[TD]670
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KLOCKNER_2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Phyllocontin® Continus® F.C Tablets
[/TD]
[/TR]
[TR]
[TD]CH150_GF
[/TD]
[TD][/TD]
[TD][/TD]
[TD] x
[/TD]
[TD] x
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Phyllocontin® Continus® F.C Tablets
[/TD]
[/TR]
[TR]
[TD]CAM
[/TD]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[TD]#VALUE!
[/TD]
[/TR]
[TR]
[TD]TR_100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] x
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Phyllocontin® Continus® F.C Tablets
[/TD]
[/TR]
[TR]
[TD]EX_240
[/TD]
[TD][/TD]
[TD][/TD]
[TD] x
[/TD]
[TD] x
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Phyllocontin® Continus® F.C Tablets
[/TD]
[/TR]
[TR]
[TD]FARCON
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]ROTOVAC
[/TD]
[TD][/TD]
[TD][/TD]
[TD] x
[/TD]
[TD] x
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Phyllocontin® Continus® F.C Tablets
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Score
[/TD]
[TD]100
[/TD]
[TD]42
[/TD]
[TD]100
[/TD]
[TD]120
[/TD]
[TD]30
[/TD]
[TD]30
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

How 'bout the expected outcomes? There are ties in your data - how will those be treated?

 

[Med4040]

My bad, I forgot about the expected outcomes
So for example there are 2 products made on CAM (marked "x"). The product with the highest score should be the outcome (Percepta®50mg Tablets).
However, if both products Percepta®50mg Tablets and Pravia® CR 200mg Tablets had a score of 100, then the outcome should be Pravia® CR 200mg Tablets because it the a smallest LD50
So the rule is, highest score and smallest LD50

This is is the formula I used

IF(A7="","",INDEX(C$1:H$1,MATCH(MAX(IF(C7:H7<>"",C$15:H$15-C$5:H$5/1000)),IF(C7:H7<>"",C$15:H$15-C$5:H$5/1000),0)))

 

[DRSteele]

Row 7 has identical data. 100 and 243.

 

[Med4040]

Row 7 has identical data. 100 and 243.

Yes, this is the same product with different batch sizes S and L, the out come in this case would be same product Phyllocontin® Continus® F.C Tablets (highest score)

 

[Med4040]

Hi DRSteele, just wanted to let you know that I figured out what was the problem. Some data was entered inconsistently (not consistent with other data).
I did that and now everything is perfect.

Thank you.