more than 4 if functions?

[Guest]

I have 2 colums of numbers and I need to find out this criteria. Do I use
the If function?

A1>29 and B1>17 = 1
A1>29 and B1<17 = 2
A1<29 and B1>17 = 3
A1<29 and B1<17 = 4
A1=29 and B1=17 = 5

 

[Guest]

One guess ..

Try in say, C1:
=IF(COUNT(A1,B1)<2,"",IF(AND(A1>29,B1>17),1,IF(AND(A1>29,B1<17),2,IF(AND(A1<29,B1>17),3,IF(AND(A1<29,B1<17),4,IF(AND(A1=29,B1=17),5,""))))))
Copy down

 

[Pete_UK]

I think there are two other conditions you've not accounted for:

A1=29 and B1<>17
A1<>29 and B1=17

Each of these could be regarded as two conditions if you want to
distinguish between B1>17 and B1<17, and A1>29 and A1<29.

Anyway, yes you can use the IF function, along the lines of:

=IF(A1=29,IF(B1=17,5,"not
defined"),IF(A1>29,IF(B1>17,1,2),IF(B1>17,3,4)))

This assumes the second and fourth of your conditions are for B1<=17.

Hope this helps.

Pete

 

[Guest]

I tried it but it came back with a True answer for all of them and I need to
say either 1,2,3,4 or 5.

 

[Niek Otten]

Hi Joy,

Your specs are incomplete. What if A1=29 and B1 is not 17?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have 2 colums of numbers and I need to find out this criteria. Do I use
| the If function?
|
| A1>29 and B1>17 = 1
| A1>29 and B1<17 = 2
| A1<29 and B1>17 = 3
| A1<29 and B1<17 = 4
| A1=29 and B1=17 = 5
| --
| Thanks, Joy

 

[Guest]

I tried it but it came back with a True answer for all of them and I need to
say either 1,2,3,4 or 5.

Not sure what happened over there. The formula will either return a blank:
"", or the numbers: 1,2,3,4,5 as per your specs depending on the contents in
A1:B1.

Suggest you just try copying the entire formula from my post, then paste
directly into the formula bar for C1.

---

 

[Guest]

I should have said that if A1=29 or B1=17 then it needs to say 5. I don't
know how this worked but it did. Thanks for your help.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

I should have said that if A1=29 or B1=17 then it needs to say 5. I don't
know how this worked but it did. Thanks for your help.
--
Thanks, Joy









- Show quoted text -

 

[joeu2004]

I have 2 colums of numbers and I need to find out this criteria.
Do I use the If function?

You could. Alternatively....

A1>29 and B1>17 = 1
A1>29 and B1<17 = 2
A1<29 and B1>17 = 3
A1<29 and B1<17 = 4
A1=29 and B1=17 = 5

[.... And you later wrote the following errata ....]

I should have said that if A1=29 or B1=17 then it needs to say 5.

The following seems to fit your corrected criteria:

=1 + AND(A1<>29,B1<17) + 2*AND(A1<29,B1<>17) + 4*OR(A1=29,B1=17)

This produces the following results:

a>29,b>17: 1
a>29,b<17: 2
a<29,b>17: 3
a<29,b<17: 4
a=29,b=17: 5
a=29,b>17: 5
a>29,b=17: 5
a=29,b<17: 5
a<29,b=17: 5

Essentially, think of the result as a binary number where bit1 is 0 or
1 based on B<17 (and A<>29), bit2 is 0 or 1 based on A>29 (and B<>17),
and bit3 is 0 or 1 based on A=29 or B=17. That would result in 0-4
since binary numbers represent the sum of powers of 2, viz. 1*bit1 +
2*bit2 + 4*bit3. Add 1 for the results 1-5.

(Note: Normally bits are numbers 0,1,2,... to match their respective
power of 2.)