+ Reply to Thread
Results 1 to 9 of 9

Using IF/AND Formula for Multiple Conditions

  1. #1
    Registered User
    Join Date
    09-18-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Using IF/AND Formula for Multiple Conditions

    I have a spreadsheet that I want a number (points) show up according to what's in Column A and Column E. Column A has a Data Validation list and in Columns C & D start and end dates are entered that calculate into months in Column E. I want Column F to equal the number points according to what is entered into Columns A & E. I tried to do a IF/AND formula that would put the correct points in Column F. I've tried the following formula's so far but to no avail...I've attached a sample copy of the spreadsheet I am working on.

    IF(AND(E5>=5, “5 or more years of 2010 (12 pts)”),12,IF(AND(E5>2<5, “>2< 5 years of 2010 (9 pts)”),9,IF(AND(E5<0>2, “>0<2 years of 2010 (6 pts)”),6,IF(AND(E5<=5, “Non SCM 5 or more years (3 pts)”),3,IF(AND(E5<5, “Non SCM less than 5 years (1 pt)”),1,0)))))

    =IF(AND(A5=“5 or more years of 2010 (12 pts)”, E5>=5),12,IF(AND(A5=“>2< 5 years of 2010 (9 pts)”, E5>2<5),9,IF(AND(A5=“>0<2 years of 2010 (6 pts)”, E5<0>2),6,IF(AND(A5= “Non SCM 5 or more years (3 pts)”, E5<=5),3,IF(AND(A5=“Non SCM less than 5 years (1 pt)”, E5<5),1,0)))))

    IF((AND(A5=“5 or more years of 2010 (12 pts)”, E5>=5),12,IF((AND(A5=“>2< 5 years of 2010 (9 pts)”, E5>2<5),9,IF((AND(A5=“>0<2 years of 2010 (6 pts)”, E5<0>2),6,IF((AND(A5= “Non SCM 5 or more years (3 pts)”, E5<=5),3,IF((AND(A5=“Non SCM less than 5 years (1 pt)”, E5<5),1,0)))))

    I am an amateur who likes trying different formulas in Excel to try and make things a little simpler. I appreciate any assistance I can get
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO200;Win10/MSO2016
    Posts
    9,787

    Re: Using IF/AND Formula for Multiple Conditions

    Maybe in F5:
    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    09-18-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Using IF/AND Formula for Multiple Conditions

    protonLeah,

    Thank you. The formula seems to be working except for the criteria "5 or more years of 2010 (12pts)". In Column F for the points it shows #N/A. I have never used a formula like this one before so I am not quite sure I understand it. I am not understanding the "LEFT($A5,3)="non",3^" part of the formula.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO200;Win10/MSO2016
    Posts
    9,787

    Re: Using IF/AND Formula for Multiple Conditions

    Given Parameter: Non SCM 5+ years = 3 pts or, Non SCM < 5 years = 1 pt

    Calculation: LEFT($A5,3)="non",3^((DATEDIF(C5,D5,"Y"))>=5)
    If the first three characters in cell An are "non" then perform the calculation: Get the date difference in years and test. Greater than [or equal] 5 returns TRUE (i.e., 1), otherwise FALSE (0).
    then 3 is raised to the calculated exponent ---> 30 --> 1 and 31--> 3

    EDIT: Added "=" in equation above.
    Last edited by protonLeah; 02-15-2018 at 05:24 PM.

  5. #5
    Registered User
    Join Date
    09-18-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Using IF/AND Formula for Multiple Conditions

    protonLeah,

    I am not sure what's happening but it still would not work for the "5 or more years of 2010 (12 pts). I've attached the spreadsheet to show what keeps happening. And thank you for the explanation of the equation. I really appreciate.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO200;Win10/MSO2016
    Posts
    9,787

    Re: Using IF/AND Formula for Multiple Conditions

    F5:
    Please Login or Register  to view this content.
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Last edited by protonLeah; 02-15-2018 at 10:52 PM.

  7. #7
    Registered User
    Join Date
    09-18-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Using IF/AND Formula for Multiple Conditions

    Okay...I guess I just don't get it because I can't get it to work just for that particular criteria. Thank you so much for you help. I truly appreciate it.

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO200;Win10/MSO2016
    Posts
    9,787

    Re: Using IF/AND Formula for Multiple Conditions

    o.k. I;ve posted the formula in F5:F10
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    09-18-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Using IF/AND Formula for Multiple Conditions

    protonLeah....Thank you sooooo much. I truly appreciate you. The formula works like a charm.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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