Nested IF AND/OR function

[jstrain]

Hi all,
I'm trying to write an formula for an audit. The questions is, if a patient is on a particular drug 'A', are certain numbers within a certain range - for instance
if they are on drug A, and they are over 80, or a serum creatinine of over 133, or a weight of under 60kg, or a creatinine clearance in the rnage 15-29mmol, then they need to have had a blood test within a certain period of time (6 months). I'm struggling with how to write it without getting an error!
my current approach is =IF(D25="A" AND(OR(L25>133,N25<60,E25>=80,(W25<=29,W25>=15)), "Y", "N") but that gets an error
A previous formula which includes the other drugs 'R' and 'D' with their particular criteria looks like this

=IF(AND(H27="R",W27<=50,W27>=14),"Y",IF(AND(H27="A",W27<=30,W27>14)*(OR(E27>=79))*(OR(L27>=132))*(OR(N27<=61)),"Y",IF(AND(H27="D",W27>=29,W27<=51),"Y","N")))

I think that is working but doesn't include the extra criteria outlined above.

Would appreciate any help

 

[JackDanIce]

Welcome to the board.

You need to break the problem down and identify indiviual parts and then link from there. Also, anyone reading can't see your spreadsheet or know the things you know. You need to explain the problem more concisely and clearly.

How many values are for H27, I read "R", "A", "D"?
What is the age range and disctinction ages (e.g. only over 80 and below or equal to 80? Adults, young adults, paeds)
What happens if serum creatine is 133 or less? Is there an overdose or danger level? And other amounts to consider
What is the weight considerations, e.g. if 60kg or more? What if it's a child, say 30kg or less? What if they're overweight, 80kg or more?

Have you tried to draw a decision tree to map the logic out before you try to convert into an Excel formula?

Much easier if you provide distinct examples with inputs and expected outputs.

 

[LMacD]

I would create a macro and put it in a module for the sheet this data is on.
I assume this is a table with multiple rows, so this macro needs work to deal with the table.
This will work with Row 27 only.
What you need is a table with column headings and then declaring the ranges using table name and heading instead of specific cell.
This is triggered only when the drug changes. Not likely what you want. Better to have a recalc button or set this to recalculate on any worksheet change.

[FONT=Verdana]Option Explicit[/FONT]
[FONT=Verdana]Private Sub Worksheet_Change(ByVal ChangedCell As Range)
    'ChangedCell is the cell on the worksheet that changed
        
    Dim DrugCell As Range
    Dim TargetCell As Range
    Dim PatientAge As Range
    Dim SerumCreatinine As Range
    Dim CreatinineClearance As Range
    
    
    ' The variable DrugCell contains the drug code and will trigger this sub whne it changes
    'I do not know what cell your formula was in so I assume A1 .
    'Change A1 to the cell the response is in
    Set TargetCell = Range("A27")
    Set DrugCell = Range("H27")[/FONT]
[FONT=Verdana]    
    Set PatientAge = Range("E27")
    Set SerumCreatinine = Range("L27")
    Set CreatinineClearance = Range("W27")[/FONT]

[FONT=Verdana]
    'Is the changed the drug cell?  If it is trigger this sub.  If not do nothing
    If Not Application.Intersect(DrugCell, Range(ChangedCell.Address)) _
           Is Nothing Then
    
        Select Case DrugCell
            Case Is = "A"
             If (CreatinineClearance <= 30 And CreatinineClearance > 14) And ((PatientAge >= 79) Or (SerumCreatinine >= 132) Or (Range("N27") <= 61)) Then
                TargetCell.Value = "Y"
             Else
                TargetCell.Value = "N"
            End If
             
            Case Is = "R"
                If CreatinineClearance <= 50 And CreatinineClearance >= 14 Then
                    TargetCell.Value = "Y"
                Else
                    TargetCell.Value = "N"
                End If
        
            Case Is = "D"
                If CreatinineClearance >= 29 And CreatinineClearance <= 51 Then
                    TargetCell.Value = "Y"
                Else
                    TargetCell.Value = "N"
                End If
        
            Case Else
                TargetCell.Value = "Not valid drug"
        End Select
    End If
    
End Sub[/FONT]
[FONT=Verdana]
[/FONT]

 

[jstrain]

Welcome to the board.

You need to break the problem down and identify indiviual parts and then link from there. Also, anyone reading can't see your spreadsheet or know the things you know. You need to explain the problem more concisely and clearly.

How many values are for H27, I read "R", "A", "D"?
What is the age range and disctinction ages (e.g. only over 80 and below or equal to 80? Adults, young adults, paeds)
What happens if serum creatine is 133 or less? Is there an overdose or danger level? And other amounts to consider
What is the weight considerations, e.g. if 60kg or more? What if it's a child, say 30kg or less? What if they're overweight, 80kg or more?

Have you tried to draw a decision tree to map the logic out before you try to convert into an Excel formula?

Much easier if you provide distinct examples with inputs and expected outputs.

Yep, sorry, it's a bit muddled!
Three values, R, A, and D.
The aim is to audit the dosage of the drug and the relevant monitoring they need while on it.

The criteria for reduced dose is different depending on the drug. So if they are on drug A, they need to be on a reduced dose if they are either 1) over 80 2) have a serum creatinine >133mmol/L 3) weight under 60kg, or 4) have a creatinine clearance (CrCl) in the range 15-29mL/min.
For drug D they need to be on a reduced dose if they are a) over 80 or b) have a CrCl of 30-50mL/min.
For drug R it is if CrCl is 15-49ml/min.
Below the bottom end of those ranges they shouldn't be on A or R if their CrCl<15ml/minute, or D if CrCl <30mL/minute.

The second thing is the frequency of blood tests, if they are on A or R and have a CrCl within a certain range then they need to have had a blood test in the past 6 months if CrCl is 30-60ml/min, and then 3 monthly if 15-30mL/min. If on D then 6 monthly in the range 30-50.


I'll put on a example tomorrow and do a decision tree, don't have access to the spreadsheet right now as it's on a work computer.

 

[JackDanIce]

Use abstraction, simplify the problem, it's still too heavy for me to quickly suggest a formula.

Also break it down, use helper columns, e.g. a column that simply says "Is serum creatine > 133" which returns a TRUE or FALSE value. Then you know that column is independent from the drug type or paitent variables. Then you can link it to a 3rd column "If drug = A AND serum > 133"

Eventually, you'll be able to spot columns you can combine i.e. nest formulas and remove those. Decision tree I think will help hugely too.


(I've consulted at a health economics before, looking at paitent pathways, efficiency of dosage, adverse reactions, bed costs, length of stay etc. so I can relate to what you're asking - it takes time to understand problems like these, even if the data manipulation isn't too bad, once understood!)