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




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)
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,047
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jan 9, 2018
Messages
55
<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:

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,047
Office Version
365
Platform
Windows
How 'bout the expected outcomes? There are ties in your data - how will those be treated?
 

Med4040

Board Regular
Joined
Jan 9, 2018
Messages
55
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:

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,047
Office Version
365
Platform
Windows
Row 7 has identical data. 100 and 243.
 
Last edited:

Med4040

Board Regular
Joined
Jan 9, 2018
Messages
55
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

Board Regular
Joined
Jan 9, 2018
Messages
55
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.
 

Forum statistics

Threads
1,081,615
Messages
5,360,037
Members
400,565
Latest member
Tommy O

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top