+ Reply to Thread
Results 1 to 5 of 5

ERROR : AND OR in EXCEL

  1. #1
    Registered User
    Join Date
    03-18-2014
    Location
    Pakistan
    MS-Off Ver
    2007
    Posts
    24

    ERROR : AND OR in EXCEL

    I have some some points (inputs and outputs) and on that basis I do a controller selection but I am facing problem at 1st stage as mentioned below:
    For controller "ABC" points limit is:10

    DO DI AI AO
    7 1 2 0
    For controller "XYZ" points limit is:=>17

    DO DI AI AO
    7 1 6 2
    In the formula below it works until the limit of controller "ABC" That is 10 and limit of controller "XYZ" that is 17 is satisfied, as soon as, I insert additional points (either in DO,DI,AI,A0) for controller "XYZ", it shows "out of range". I can simply change "Out of range" to "XYZ" but I want to do it via formula.

    =IF(AND(I29/D21<=17,E29/D21<=7,F29/D21<=2,G29/D21<=6,H29/D21<=2,(E29+G29+H29)/D21<=15,(F29+G29+H29)/D21<=10),"ABC",IF(AND(I29/D21<=17,E29/D21<=7,F29/D21<=2,G29/D21<=6,H29/D21<=2,(E29+G29+H29)/D21<=15,(F29+G29+H29)/D21<=10),"XYZ","Out of Range"))
    The table is shown below.

    A B C D E F G H I
    1 EQUIPMENT Description QTY DO DI AI AO Total Points
    2 INPUTS Any Input 1 7 2 6 2
    3 XYZ Controller 1
    4 Total Points 7 2 6 2 17
    Here selection is Ok, as per the formula Controller XYZ is selected

    BUT

    ERROR: if any point is increased in E2/F2/G2/H2

    A B C D E F G H I
    1 EQUIPMENT Description QTY DO DI AI AO Total Points
    2 INPUTS Any Input 1 7 3 6 2
    3 XYZ Controller 1
    4 Total Points 7 3 6 2 18

    Please Help... BrExcel Question.jpg
    Last edited by AasifKhan; 07-23-2016 at 04:12 AM. Reason: Adding Attachment

  2. #2
    Valued Forum Contributor abduljaleel.mca's Avatar
    Join Date
    02-13-2013
    Location
    Chennai, India
    MS-Off Ver
    MS 365 Business
    Posts
    326

    Re: ERROR : AND OR in EXCEL

    Hi,
    Attach a sample file with descriptions of exact problem in it.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: ERROR : AND OR in EXCEL

    It seems to me that both arguments you have there, are the same...
    =
    IF(AND(I29/D21<=17,E29/D21<=7,F29/D21<=2,G29/D21<=6,H29/D21<=2,(E29+G29+H29)/D21<=15,(F29+G29+H29)/D21<=10),"ABC",
    IF(AND(I29/D21<=17,E29/D21<=7,F29/D21<=2,G29/D21<=6,H29/D21<=2,(E29+G29+H29)/D21<=15,(F29+G29+H29)/D21<=10),"XYZ","Out of Range"))

    Also, it looks like you dont close the 1st IF?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    03-18-2014
    Location
    Pakistan
    MS-Off Ver
    2007
    Posts
    24

    Re: ERROR : AND OR in EXCEL

    Abdul Jameel and Dibbins

    Find attached excel sheet..
    Attached Files Attached Files

  5. #5
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: ERROR : AND OR in EXCEL

    Try to use data validation
    For E2, F2, G2, H2
    using formula
    =AND(E2<=17-(F2+G2+H2),E2>=0)
    =AND(F2<=17-(E2+G2+H2),F2>=0)
    =AND(G2<=17-(E2+F2+H2),G2>=0)
    =AND(H2<=17-(E2+F2+G2),H2>=0)
    and in tab Error use "Out of range"
    You can copy this data validation to other range.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA ERROR: run time error 1004: Application-defined or Object-defined error in excel 2013
    By AnanthKrishna in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-13-2015, 06:16 AM
  2. MACRO ERROR WITH EXCEL 2013 - Run-Time Error '-2147417848 (80010108)'
    By graiggoriz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-29-2014, 11:07 AM
  3. Excel macro (compile error. syntax error.) error
    By salar_younis in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-06-2014, 06:11 AM
  4. Facing 'run-time error -2147217871 (80040e31) Automation error' in excel VBA
    By junaidanjum in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-25-2013, 03:48 AM
  5. Replies: 7
    Last Post: 01-15-2010, 02:34 PM
  6. Excel xmlHTTP object error message - system/runtime error
    By Porky2007 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-23-2007, 09:36 AM
  7. Replies: 3
    Last Post: 02-13-2006, 11:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1