Excel - Nested IF Statement

cloud_dog

Hi

I apologise for asking another nested IF statement question but I seem to be going round and round in circles and am failing to resolve

I have a list of 'items' and against each item the user can enter a code (between 1 and 10; all good here). After entering the code value they may need to enter a sub-code value. To the right of the sub-code cell I have a confirmation cell displaying an error message if appropriate.

So, codes 2, 3, 6, and 9 require sub-code values. And, each of these has varying sub-code ranges. So:

2 - 01 to 04 inc.
3 - 01 to 17 inc.
6 - 01 to 04 inc.
9 - 01 to 06 inc

Basically if the user enters a code of 2 then I want to display an error if they do not enter a sub-code of 01 to 04 inc.

Additionally, I need to utilise as basic an IF statement as possible. By that I mean I cannot use IFS as I cannot guarantee users will have a version of Excel that supports it, and I cannot use macros/programming as they are disabled in the user community.

My statement is below. D54 is the Code cell, E54 is the sub-code cell, and this statement is in F54.

=IF(D54=2,IF(OR(E54<201,E54>204,"Invalid Sub-Code Entered","")),IF(D54=3,IF(OR(E54<301,E54>317,"Invalid Sub-Code Entered","")),IF(D54=6,IF(OR(E54<601,E54>604,"Invalid Sub-Code Entered","")),IF(D54=9,IF(OR(E54<901,E54>906,"Invalid Sub-Code Entered",""))))))

I get an error.

Also a little unsure when IF OR / AND statements were introduced. If it was Excel 2010 of later I may need to regress to a long winded IF statement.

Any help would be greatly appreciated.

rmg1

Try this one:-
=IF(D54=2,IF(OR(E54<201,E54>204),"Invalid Sub-Code Entered",IF(D54=3,IF(OR(E54<301,E54>317),"Invalid Sub-Code Entered",IF(D54=6,IF(OR(E54<601,E54>604),"Invalid Sub-Code Entered"),IF(D54=9,IF(OR(E54<901,E54>906),"Invalid Sub-Code Entered","")))))),"")

You'd just got your brackets in a muddle.

cloud_dog

Hi

Thanks for that. Certainly got rid of the error.

Unfortunately, my logic is flawed also as I cannot get the error message to appear if I enter an incorrect sub-code for the 3, 6, or 9 codes :eek:

rmg1

According to the logic in your OP, this should work:-
=IF(OR(AND(D54=2,OR(E54<201,E54>204)),AND(D54=3,OR(E54<301,E54>317)),AND(D54=6,OR(E54<601,E54>604)),AND(D54=9,OR(E54<901,E54>906))),"Invalid Sub-Code","")

NaughtiusMaximus

Try this:

=IF(AND(D54=2,OR(E54<201,E54>204)),"Invalid Sub-Code Entered",IF(AND(D54=3,OR(E54<301,E54>317)),"Invalid Sub-Code Entered",IF(AND(D54=6,OR(E54<601,E54>604)),"Invalid Sub-Code Entered",IF(AND(D54=9,OR(E54<901,E54>906)),"Invalid Sub-Code Entered",""))))

I've added AND statements, I think IF, AND and OR statements were introduced well before Office 2010 but I'm not absolutely sure.

rmg1

They've been around since Excel 95 (which is the version I first started with).

Linton

I think you want
=IF(D54=2,IF(OR(E54<201,E54>204),"Invalid Sub-Code Entered",""),
IF(D54=3,IF(OR(E54<301,E54>317),"Invalid Sub-Code Entered",""),
IF(D54=6,IF(OR(E54<601,E54>604),"Invalid Sub-Code Entered",""),
IF(D54=9,IF(OR(E54<901,E54>906),"Invalid Sub-Code Entered",""),"D54 is not 2 3 6 OR 9"))))

WaywardDriver

Alternative to nested IFs which should be easier to maintain if valid codes change in future:

enter valid values of 100*MainCode+SubCode into adjacent cells in a column
i.e. 100, 201,202,203,204,301...317...906,1000

select these cells, right-click, Define Name as OKAY say

then if MainCode in col A and SubCode in col B, enter formula in C1
=IF(ISNA(MATCH(100*A1+B1,OKAY,0)),"Invalid Sub-code","")

and copy down column

Heedtheadvice

No time to reply in full just at the moment but sould suggest another solution instead of convoluted if statements! They will be a right bu**er to maintain should you have an error or wish to change subcodes!


Suggest having lists: for each main code with all the applicable subcodes within each list.
1 you can use lookup and match functions to do the texts (instead of holding the applicable codes in your if functions) -far easier to read and understand the test;
2 if you need to change the subcodes (either for testing your worksheet or updates/changes) it is easy to see what is appropriate to each main code;
3 Reduces errors in the 'coding' aspects such ad you are experiencing.


(or you could have one big list with every valid entry for each main code in a two column list!.....many ways to skin a cat . Your list or lists could be on a separate sheet and even hidden or protected)

cloud_dog

Many thanks to everyone's comments and suggestions, it is very much appreciated.

As mentioned I think I consciously / sub consciously stuck with IF statements simply to remove the risk of isuues due to older versions of Excel in place. I agree possible ongoing maintenance is an aspect that would benefit from a different approach.