Formula gone wrong after it was working fine

Med4040

Board Regular
Joined
Jan 9, 2018
Messages
55
Hi guys, I used this formula before and that I had to make some changes, it stopped functioning the way it supposed to


16k9j5e.png


in I7, this is the formula
Code:
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)
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.
 
Upvote 0
<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
Equipment Name
Percepta®50mg Tablets
Percepta®50mg Tablets
Phyllocontin® Continus® F.C Tablets
Phyllocontin® Continus® F.C Tablets
Pravia® CR 200mg Tablets
Pravia® CR 300mg Tablets
Worst Case Product (A)
Batch Size
S
L
S
L
S&L
S&L
Batch Size, kg
13.12
26.25
54.70
109.40
48.00
72.00
MDD, mg
1200
1200
700
700
600
900
LD50, mg/kg
1024
1024
243
243
670
670
KLOCKNER_2
x
Phyllocontin® Continus® F.C Tablets
CH150_GF
x
x
Phyllocontin® Continus® F.C Tablets
CAM
x
x
#VALUE!
TR_100
x
Phyllocontin® Continus® F.C Tablets
EX_240
x
x
Phyllocontin® Continus® F.C Tablets
FARCON
#N/A
ROTOVAC
x
x
Phyllocontin® Continus® F.C Tablets
Score
100
42
100
120
30
30

<tbody>
</tbody>
 
Last edited:
Upvote 0
How 'bout the expected outcomes? There are ties in your data - how will those be treated?
 
Upvote 0
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
Code:
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)))
 
Last edited:
Upvote 0
Row 7 has identical data. 100 and 243.
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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