Merging formula not working

[Bishnu Adhikari]

I have 2 conditions (CaseA and CaseB) that I need to merge but the merging formula is not working, it returns #VALUE!. They are working separately, however. Any suggestion is highly appreciated. The file is attached herewith.

For CaseA:
=IF($B2="CaseA",IF($D2<24,"Invalid",IF(AND($E2<34,$D2>=24,$D2<336,$C2>=0),"Indeterminate",IF(AND($E2<34,$D2>336,$C2>5.5),"Normal",IF(AND($E2<34,$D2>336,$C2<5.5),"High Risk",IF(AND($E2>=34,$D2>=24,$C2>10),"Normal",IF(AND($E2>=34,$D2>=24,$D2<167,$C2>8,$C2<=10),"Borderline",IF(OR(AND($E2>=34,$D2>=24,$D2<=167,$C2<8),AND($E2>=34,$D2>=168,$D2<=335,$C2<6),AND($E2>=34,$D2>=336,$C2<5.5)),"Refer"))))))))

For CaseB:
=IF($B2="CaseB",IF($D2<24,"Invalid",IF(AND($E2<34,$D2>=24,$D2<336,$C2>=0),"Indeterminate",IF(AND($E2<34,$D2>336,$C2>3.2),"Normal",IF(AND($E2<34,$D2>336,$C2<3.2),"High Risk",IF(AND($E2>=34,$D2>=24,$C2>6),"Normal",IF(AND($E2>=34,$D2>=24,$D2<168,$C2>4,$C2<=6),"Borderline",IF(OR(AND($E2>=34,$D2>=24,$D2<=167,$C2<4),AND($E2>=34,$D2>=168,$D2<=335,$C2<3.6),AND($E2>=34,$D2>=336,$C2<3.2)),"Refer"))))))))

Merged:
=IF($B2="CaseA",IF($D2<24,"Invalid",IF(AND($E2<34,$D2>=24,$D2<336,$C2>=0),"Indeterminate",IF(AND($E2<34,$D2>336,$C2>5.5),"Normal",IF(AND($E2<34,$D2>336,$C2<5.5),"High Risk",IF(AND($E2>=34,$D2>=24,$C2>10),"Normal",IF(AND($E2>=34,$D2>=24,$D2<167,$C2>8,$C2<=10),"Borderline",IF(OR(AND($E2>=34,$D2>=24,$D2<=167,$C2<8),AND($E2>=34,$D2>=168,$D2<=335,$C2<6),AND($E2>=34,$D2>=336,$C2<5.5)),"Refer")))))))), IF($B2="CaseB",IF($D2<24,"Invalid",IF(AND($E2<34,$D2>=24,$D2<336,$C2>=0),"Indeterminate",IF(AND($E2<34,$D2>336,$C2>3.2),"Normal",IF(AND($E2<34,$D2>336,$C2<3.2),"High Risk",IF(AND($E2>=34,$D2>=24,$C2>6),"Normal",IF(AND($E2>=34,$D2>=24,$D2<168,$C2>4,$C2<=6),"Borderline",IF(OR(AND($E2>=34,$D2>=24,$D2<=167,$C2<4),AND($E2>=34,$D2>=168,$D2<=335,$C2<3.6),AND($E2>=34,$D2>=336,$C2<3.2)),"Refer"))))))))

[nick.williams]

Maybe solved already from the above comment? But if not, you have a bracket in the wrong place. Delete one closing bracket from the end of the first IF statement (just before IF($B2="CaseB") and add one to the end of the formula, then it works.

[FDibbins]

If you only have 2 choices for the Case, you dont need the IF($B2="CaseB"...part

it would read something like...
=IF($B2="CaseA",IF($D2<24,"Invalid",IF(AND($E2<34,$D2>=24,$D2<336,$C2>=0),"Indeterminate",IF(AND($E2<34,$D2>336,$C2>5.5),"Normal",IF(AND($E2<34,$D2>336,$C2<5.5),"High Risk",IF(AND($E2>=34,$D2>=24,$C2>10),"Normal",IF(AND($E2>=34,$D2>=24,$D2<167,$C2>8,$C2<=10),"Borderline",IF(OR(AND($E2>=34,$D2>=24,$D2<=167,$C2<8),AND($E2>=34,$D2>=168,$D2<=335,$C2<6),AND($E2>=34,$D2>=336,$C2<5.5)),
IF($D2<24,"Invalid",IF(AND($E2<34,$D2>=24,$D2<336,$C2>=0),"Indeterminate",IF(AND($E2<34,$D2>336,$C2>3.2),"Normal",IF(AND($E2<34,$D2>336,$C2<3.2),"High Risk",IF(AND($E2>=34,$D2>=24,$C2>6),"Normal",IF(AND($E2>=34,$D2>=24,$D2<168,$C2>4,$C2<=6),"Borderline",IF(OR(AND($E2>=34,$D2>=24,$D2<=167,$C2<4),AND($E2>=34,$D2>=168,$D2<=335,$C2<3.6),AND($E2>=34,$D2>=336,$C2<3.2)),"Refer"))))))))

untested, so may have some ) missing at the end, and the whol;e thing copuld probably be simplified a bit.

[FDibbins]

Maybe this...untested

Please Login or Register  to view this content.

[Bishnu Adhikari]

Thank you so much, FDibbins.

[FDibbins]

Happy to help

[nick.williams]

It certainly looks like it could be made shorter. Also there are some "gaps" in the logic. Firstly there are some cases where the > and <= values either overlap or leave gaps.

Secondly, the following values are not dealt with:

C<0 when D<336 and E<34
C between 6 and 10 when D>=168 and E>=34
C between 5.5 and 10 when D>=336 and E>=34

I've made the boundaries consistent and put the criteria in tables. Then the combined formula is:

Please Login or Register  to view this content.

Admittedly not that much shorter but it does make it a million times easier to see what's going on and to change things. It gives the same results as your examples.