+ Reply to Thread
Results 1 to 7 of 7

IF,AND Statements, and maybe drop down list

  1. #1
    Registered User
    Join Date
    01-20-2011
    Location
    alabama
    MS-Off Ver
    Excel 2003
    Posts
    12

    IF,AND Statements, and maybe drop down list

    Hi Everyone,

    Im new to this site, but I have not been able to figure out this problem yet, so any help would be great!

    Here it is,

    I have in column A1 to A3 companies A,B,C.. These companies may be switched around (B,C,A) depending on which company is there at the time.

    Column B1 to B3 will correspond to days, (could be anything from 1 to infinity)

    Column C1 to C3 will be the main formula (anything from 0 to infinity again)

    My problem is, I can make an if AND statement for each individual company, but its when they start changing around that it gets tricky for me.

    I would like in the end, for the user to pick a company (A,B,C) and that corresponding Formula to be used in column C.

    For example , here are my if AND statements

    Company A : IF(AND(A:A="A",B:B<14),0,B:B-14)
    Company B : IF(AND(A:A="B",B:B<14),0,B:B-14)
    Company C: IF(AND(A:A="C",B:B<10),0,B:B-10)

    These formulas will be used in Column C.. Is there a way to combine all these if statements in one single long statement.. I would like the user to just choose which company and how many days in column B, and the appropriate formula choosen

    Thank you for all the help!!!

  2. #2
    Registered User
    Join Date
    01-20-2011
    Location
    alabama
    MS-Off Ver
    Excel 2003
    Posts
    12

    If AND Statements

    Hi Everyone,

    Im new to this site, but I have not been able to figure out this problem yet, so any help would be great!

    Here it is,

    I have in column A1 to A3 companies A,B,C.. These companies may be switched around (B,C,A) depending on which company is there at the time.

    Column B1 to B3 will correspond to days, (could be anything from 1 to infinity)

    Column C1 to C3 will be the main formula (anything from 0 to infinity again)

    My problem is, I can make an if AND statement for each individual company, but its when they start changing around that it gets tricky for me.

    I would like in the end, for the user to pick a company (A,B,C) and that corresponding Formula to be used in column C.

    For example , here are my if AND statements

    Company A : IF(AND(A:A="A",B:B<14),0,B:B-14)
    Company B : IF(AND(A:A="B",B:B<14),0,B:B-14)
    Company C: IF(AND(A:A="C",B:B<10),0,B:B-10)

    These formulas will be used in Column C.. Is there a way to combine all these if statements in one single long statement.. I would like the user to just choose which company and how many days in column B, and the appropriate formula choosen

    Thank you for all the help!!!

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: If AND Statements

    Perhaps you just want:

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    01-20-2011
    Location
    alabama
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: If AND Statements

    Hi,


    OMG! i would of never done it that way!! Thank you sooo much!!!!!

    So now i can just change the company (A,B,C) and the formula will recognize my restraints!!

    One more question:

    Say i change one condition in my second if statement for company B from 14 to say 18.. or any number,, how different would your code read?

    Thanks alot!!
    Last edited by Rieck2000; 01-20-2011 at 07:23 PM.

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: If AND Statements

    Considering just 3 companies:

    Please Login or Register  to view this content.
    or

    Please Login or Register  to view this content.
    the latter would throw an error if A1 were neither A nor B nor C.

  6. #6
    Forum Contributor
    Join Date
    08-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    149

    Re: IF,AND Statements, and maybe drop down list

    Since your "then" portion of each formula is zero and since only one can be true at a time then simply string them all together using plus signs ( + ).

    =IF(AND(A:A="A",B:B<14),0,B:B-14) + IF(AND(A:A="B",B:B<14),0,B:B-14) + IF(AND(A:A="C",B:B<10),0,B:B-10)

    Edit: hmm, actually the way those are written, could have more than one true at a time... would need to rewrite them so only one true at a time for my suggestion to work.

    Perhaps by changing your equalities and flipflopping the "then" and "else" parts:
    =IF(AND(A:A="A",B:B>=14),B:B-14,0) + IF(AND(A:A="B",B:B>=14),B:B-14,0) + IF(AND(A:A="C",B:B>=10),B:B-10,0)
    Last edited by masteff; 01-21-2011 at 04:26 PM.

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: IF,AND Statements, and maybe drop down list

    @masteff, this thread has since been duplicated:

    http://www.excelforum.com/excel-gene...tatements.html

    I did not notice the duplicate at the time but the above thread has had a few responses and as such I will close this thread
    [edit: I will merge but note posts will appear a little out of sync]

    @Rieck2000 - please be sure to read and follow the Board Rules when posting
    Last edited by DonkeyOte; 01-21-2011 at 04:47 PM.

+ 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