Results 1 to 14 of 14

Thread: IF(OR) (2000)

  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    IF(OR) (2000)

    I'm feeling kinda dumb this morning <img src=/S/smile.gif border=0 alt=smile width=15 height=15> I left my excel book at home, so I need your help.

    I would like to say:

    IF A3&A6&A9&A10 = CCCC or CCC0 or CC00 or C000 then "All Company" IF Not then IF A3&A6&A9&A10 = FFFF or FFF0 or FF00 of F000 then "All Franchise"

    So what's the proper syntax for this?

    If I do this with If's I will use up my allotted 7 nested IF statements, I could do this in two columns then blend the columns together, but I thought it would be a better idea to learn how to do this right..

    Help anyone..

    Thanks!

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IF(OR) (2000)

    Are the options for the cells restricted to C and 0 in the first set, and then F and 0 in the second, or can a A3&A6&A9&A10 = C0FC or even CBGH or any other combination ?

    Andrew C

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: IF(OR) (2000)

    It is possible for the combination to equal CF00 or FCFC (there are only 3 items that the cell can be C, F or 0) but we only need to take into account the items I mentioned..

    Leslie

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IF(OR) (2000)

    I think the following formula using 2 IFs should work :

    =IF(FIND("F",(A3&A6&A9&A10&"F"))>4,"All Company",IF(FIND("C",(A3&A6&A9&A10&"C"))>4,"All Franchise",""))

    Give it a try.

    Andrew

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: IF(OR) (2000)

    Wow.. That totally worked.

    I had done the problem with 2 IF statements in different columns and then concatenated them together..

    I just tested your formula against it and I got the exact same things..

    I've never used the FIND statement.. Think I'll have to look into that.

    Thank you Andrew..

    BUT..... I'd still like to know the proper syntax for a IF/OR statement <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF(OR) (2000)

    <pre>=IF(OR(A3&A6&A9&A10 =" CCCC",A3&A6&A9&A10 ="CCC0",A3&A6&A9&A10 ="CC00",A3&A6&A9&A10 ="C000"),"All Company",IF(OR(A3&A6&A9&A10 ="FFFF",A3&A6&A9&A10 ="FFF0",A3&A6&A9&A10 ="FF00",A3&A6&A9&A10 ="F000"),"All Franchise",""))
    </pre>

    Legare Coleman

  7. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: IF(OR) (2000)

    Thank you Legare,

    Now I've learned two things today!

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IF(OR) (2000)

    A variation using IF, OR, AND and arrays<pre>=IF(AND(OR(A3={"C","O"}),OR(A6={"C","O" }),OR(A9={"C","O"}),OR(A10={"C","O"})),"All Company",IF(AND(OR(A3={"F","O"}),OR(A6={"F","O"}), OR(A9={"F","O"}),OR(A10={"F","O"})),"All Franchise",""))</pre>

    Actually I am just trying to see if I can create one as long as Legare

    Edit : Looks like back to the drawing board

    Andrew

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: IF(OR) (2000)

    You want inefficiency? Let's see if this does it:

    <pre>=CHOOSE(1+(A3&A4&A5&A6 =" CCCC")+(A3&A4&A5&A6="CCC0")*2+(A3&A4&A5&A6="CC00") *3+(A3&A4&A5&A6 ="C000")*4+(A3&A4&A5&A6="FFFF")*5+(A3&A4&A5&A6 ="FFF0")*6+(A3&A4&A5&A6="FF00")*7+(A3&A4&A5&A6 ="F000")*8,"","All Company","All Company","All Company","All Company","All Franchise","All Franchise","All Franchise","All Franchise")</pre>


    I have actually used this kind of construct where each of the results is different (versus this one where there are only three results).
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IF(OR) (2000)

    John,

    I had to scroll for too long to find the reply button !

    Can you expand it to include COCC, COOC, etc. Cover all permutations and combinations. That should set a new record.

    Andrew

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: IF(OR) (2000)

    I think the bandwidth police would kick my butt for that. <img src=/S/bif.gif border=0 alt=bif width=70 height=28> <img src=/S/wartgun.gif border=0 alt=wartgun width=73 height=24>
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IF(OR) (2000)

    Just for the record, I notice that in both your formula and Legare's the first comparison deals with " CCCC", i.e. includes a preceeding space. Has that a significance that escapes me ?

    Andrew

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: IF(OR) (2000)

    <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    I think it's an artifact of the Lounge, possibly pre tags; I made sure I eliminated all spaces in non-text parts of the formula. (I ripped off Legares formula to do my monster one, and it copied in with spaces).
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF(OR) (2000)

    My aim was not too good when I click on what I pasted from the original message to insert the double quote.
    Legare Coleman

Posting Permissions

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