Thread: If, then formula

Hello again... I am writing an if then formula for use in my research database. The goal with this formula is to assess whether or not a dosage is correct for a calculated value.

If CrCl is > 30, dosages of 40, 2.5, and 5000 (which represent dosages of various drugs) are appropriate.
If CrCl is < 30, dosages of 30 and 5000 are appropriate. Dosages of 40 or 2.5 are not appropriate.

If dosage is appropriate, print "Y", else print "N"

So, in short, I have a critical value (30). Above this value there is a Y or N question. Below this value, there is a different Y or N question. Whichever question is asked, I want it to print a Y or N (above or below the value is irrelevant, only the accuracy of the Y or N question).

I am using a COUNTIF statement to tally the number of "Y"s.

What do you ladies and gents think?

Assuming your CrCl value is in column C and your dosage value is in column D then:

=IF(ISNA(MATCH(D2,IF(C2>30,{40,2.5,5000},{30,5000}),0)),"N","Y")

You didn't specify what to do if CrCl = 30, the above formula will treat is as being <30 (i.e. dosages of 30 & 5000 are appropriate.

Thank you for the quick reply. CrCl is a calculated value in ml/min, usually with two decimal places that can be rounded... I think the odds of coming across a true "30" are limited, but I appreciate the heads up. If CrCl were 30, I would call it less than 30, being on the side of caution. Not sure to add that to the above code.

I will try this code today in the database (after PM rounds) - thanks for all of the help.

I've hit a minor snag...

The formula above correctly answers the above if then statement correctly. Unfortunately, it reports a "Y" if CrCl is div/0 (which means nothing has been entered for SCr to calculate CrCl). Since SCr isn't gathered for each patient, there are days where CrCl will be div/0.

Can anyone help me so that it always reports a "N" if there is div/0 for CrCl?

Thanks for all of the help.

Not a problem - change your formula to =IF(ISERROR(C2),"N",IF(ISNA(MATCH(D2,IF(C2>30,{40,2.5,5000},{30,5000}),0)),"N","Y"))