IF formula advice please

Hi eeliashar,

There is a much easier way to go about this. 8 nested IFs are easily 5 more than you want to deal with and we can see why here.

You want a lookup table. A lot easier to debug and easier computationally. The lookup table does not need Column A or Row 1, just the 9 cells highlighted in the formula.

The IF statement checks if B is "M". I didn't know what accuracy your ratio was coming in at so you may want to tweak the ratio risk columns in the lookup table.

The formula looks looks this:

IF(B2="m",HLOOKUP(C2,Lookup::B2:D4,3,TRUE),HLOOKUP(C2,Lookup::B3:D4,2,TRUE))

Hope this helps.

quinn

PS

Your formula. above is 2 distinct nested IF formulas seperated by a comma. This is your error.

=IF(C2="m",IF(C5>1,"high",IF(C5≥0.9,"Moderate",IF(C5<0.9,"Low")))),

IF(C2="f",IF( C5>0.85,"High",IF(C5≥0.8,"Moderate",IF(C5<0.8,"Low",""))))

What you are after is 1 nested IF. The comma at the end of the first line above would be after "Low" and the second line would be after that, within the )))). Your formula would end with )))))))).

There is a reason that nested IFs are avoided above 2 or 3 tomes and this is part of it.

q

Hi eeliashar,

I am assuming you are using Numbers 3.

I am a big fan of clicking whenever possible so I type until Numbers offers me the chance to click for an input. I am not sure what you are doing to go astray but I will give you my step by step.

Type "=" in the cell, the function window opens. By the time I type "HL" (I used caps for clarity) Numbers wants to fill "HLOOKUP" so I hit enter. HLOOKUP is ready for input.

"search-for" is already highlighted so I click C2 in my above example (Table A, the patient's ratio).

Click "rows-range" then click B2 (lookup table) in my example. Drag the lower handle on that cell to highlight the entire range by dragging it to D4.

Click return row. For the "m" we want the third row in our selection. Type "3".

We want "close-match" so we are done. click the checkmark.

It things go astray, take a sceenshot by typing cmd-shift-4 and load it up using the camera icon in the reply window.

quinn

Hi ee,

The formula looks good to me. If if works with moderate and high values then the problem is in your lookup table. The low values should = 0. It has always seemed pecular, in practice, to me how LOOKUP does its close-match. Here is the explanation, copied from the function window:

close match (TRUE, 1, or omitted): If there’s no exact match, select the column with the largest top-row value that is less than or equal to the search value.

When I read it that way it does make sense.

quinn

no problem

quinn