1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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>

7. ## Re: IF(OR) (2000)

Thank you Legare,

Now I've learned two things today!

8. ## 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. ## 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).

10. ## 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. ## 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>

12. ## 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. ## 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).

14. ## 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.

#### Posting Permissions

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