Your browser isn't supported
It looks like you're using an old web browser. To get the most out of the site and to ensure guides display correctly, we suggest upgrading your browser now. Download the latest:

Welcome to the MSE Forums

We're home to a fantastic community of MoneySavers but anyone can post. Please exercise caution & report spam, illegal, offensive or libellous posts/messages: click "report" or email forumteam@. Skimlinks & other affiliated links are turned on

Search
  • FIRST POST
    • cloud_dog
    • By cloud_dog 14th Sep 18, 11:14 AM
    • 3,838Posts
    • 2,280Thanks
    cloud_dog
    Excel - Nested IF Statement
    • #1
    • 14th Sep 18, 11:14 AM
    Excel - Nested IF Statement 14th Sep 18 at 11:14 AM
    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,"Inva lid Sub-Code Entered","")),IF(D54=6,IF(OR(E54<601,E54>604,"Inva lid Sub-Code Entered","")),IF(D54=9,IF(OR(E54<901,E54>906,"Inva lid 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.
    Personal Responsibility - Sad but True

    Sometimes.... I am like a dog with a bone
Page 1
    • rmg1
    • By rmg1 14th Sep 18, 12:53 PM
    • 2,935 Posts
    • 761 Thanks
    rmg1
    • #2
    • 14th Sep 18, 12:53 PM
    • #2
    • 14th Sep 18, 12:53 PM
    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.
    Flagellation, necrophilia and bestiality - Am I flogging a dead horse?

    Any posts are my opinion and only that. Please read at your own risk.
    • cloud_dog
    • By cloud_dog 14th Sep 18, 1:52 PM
    • 3,838 Posts
    • 2,280 Thanks
    cloud_dog
    • #3
    • 14th Sep 18, 1:52 PM
    • #3
    • 14th Sep 18, 1:52 PM
    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
    Personal Responsibility - Sad but True

    Sometimes.... I am like a dog with a bone
    • rmg1
    • By rmg1 14th Sep 18, 2:24 PM
    • 2,935 Posts
    • 761 Thanks
    rmg1
    • #4
    • 14th Sep 18, 2:24 PM
    • #4
    • 14th Sep 18, 2:24 PM
    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","")
    Flagellation, necrophilia and bestiality - Am I flogging a dead horse?

    Any posts are my opinion and only that. Please read at your own risk.
    • NaughtiusMaximus
    • By NaughtiusMaximus 14th Sep 18, 2:27 PM
    • 1,044 Posts
    • 2,569 Thanks
    NaughtiusMaximus
    • #5
    • 14th Sep 18, 2:27 PM
    • #5
    • 14th Sep 18, 2:27 PM
    Try this:

    =IF(AND(D54=2,OR(E54<201,E54>204)),"Invalid Sub-Code Entered",IF(AND(D54=3,OR(E54<301,E54>317)),"Invali d Sub-Code Entered",IF(AND(D54=6,OR(E54<601,E54>604)),"Invali d Sub-Code Entered",IF(AND(D54=9,OR(E54<901,E54>906)),"Invali d 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
    • By rmg1 14th Sep 18, 2:38 PM
    • 2,935 Posts
    • 761 Thanks
    rmg1
    • #6
    • 14th Sep 18, 2:38 PM
    • #6
    • 14th Sep 18, 2:38 PM
    They've been around since Excel 95 (which is the version I first started with).
    Flagellation, necrophilia and bestiality - Am I flogging a dead horse?

    Any posts are my opinion and only that. Please read at your own risk.
    • Linton
    • By Linton 14th Sep 18, 2:44 PM
    • 9,702 Posts
    • 9,940 Thanks
    Linton
    • #7
    • 14th Sep 18, 2:44 PM
    • #7
    • 14th Sep 18, 2:44 PM
    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
    • By WaywardDriver 14th Sep 18, 4:09 PM
    • 127 Posts
    • 115 Thanks
    WaywardDriver
    • #8
    • 14th Sep 18, 4:09 PM
    • #8
    • 14th Sep 18, 4:09 PM
    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
    Last edited by WaywardDriver; 14-09-2018 at 4:39 PM.
    • Heedtheadvice
    • By Heedtheadvice 15th Sep 18, 11:19 AM
    • 896 Posts
    • 442 Thanks
    Heedtheadvice
    • #9
    • 15th Sep 18, 11:19 AM
    • #9
    • 15th Sep 18, 11:19 AM
    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)
    Last edited by Heedtheadvice; 15-09-2018 at 11:23 AM. Reason: adds
    • cloud_dog
    • By cloud_dog 16th Sep 18, 12:08 AM
    • 3,838 Posts
    • 2,280 Thanks
    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.
    Personal Responsibility - Sad but True

    Sometimes.... I am like a dog with a bone
Welcome to our new Forum!

Our aim is to save you money quickly and easily. We hope you like it!

Forum Team Contact us

Live Stats

3,741Posts Today

8,083Users online

Martin's Twitter
  • RT @Dora_Haf: @MartinSLewis So many people on here saying they're great until you get your PROPER job. What if Your proper job Is ON zero?

  • RT @hslt88: @MartinSLewis I?m a trustee for a youth charity. We only have a limited pool of funds for flexible youth workers for holiday sc?

  • RT @Dan_i_elle_88: @MartinSLewis Loved working zero hour agency care work. Never out of work and I loved having the flexibility! Only left?

  • Follow Martin