more than 4 if functions?
 

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

A129 and B117 = 1
A129 and B1<17 = 2
A1<29 and B117 = 3
A1<29 and B1<17 = 4
A1=29 and B1=17 = 5
--
Thanks, Joy

more than 4 if functions?
 

One guess ..

Try in say, C1:
=IF(COUNT(A1,B1)<2,"",IF(AND(A129,B117),1,IF(AND (A129,B1<17),2,IF(AND(A1<29,B117),3,IF(AND(A1<29 ,B1<17),4,IF(AND(A1=29,B1=17),5,""))))))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Joy" wrote:
I have 2 colums of numbers and I need to find out this criteria. Do I use
the If function?

A129 and B117 = 1
A129 and B1<17 = 2
A1<29 and B117 = 3
A1<29 and B1<17 = 4
A1=29 and B1=17 = 5
--
Thanks, Joy

more than 4 if functions?
 

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 B117 and B1<17, and A129 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(A129,IF(B117,1,2),IF(B117,3,4)))

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

Hope this helps.

Pete

On Oct 1, 2:48 pm, Joy wrote:
I have 2 colums of numbers and I need to find out this criteria. Do I use
the If function?

A129 and B117 = 1
A129 and B1<17 = 2
A1<29 and B117 = 3
A1<29 and B1<17 = 4
A1=29 and B1=17 = 5
--
Thanks, Joy

more than 4 if functions?
 

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.
--
Thanks, Joy


"Max" wrote:

One guess ..

Try in say, C1:
=IF(COUNT(A1,B1)<2,"",IF(AND(A129,B117),1,IF(AND (A129,B1<17),2,IF(AND(A1<29,B117),3,IF(AND(A1<29 ,B1<17),4,IF(AND(A1=29,B1=17),5,""))))))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Joy" wrote:
I have 2 colums of numbers and I need to find out this criteria. Do I use
the If function?

A129 and B117 = 1
A129 and B1<17 = 2
A1<29 and B117 = 3
A1<29 and B1<17 = 4
A1=29 and B1=17 = 5
--
Thanks, Joy

more than 4 if functions?
 

Hi Joy,

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

more than 4 if functions?
 

"Joy" wrote:
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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

more than 4 if functions?
 

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


"Pete_UK" wrote:

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 B117 and B1<17, and A129 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(A129,IF(B117,1,2),IF(B117,3,4)))

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

Hope this helps.

Pete

On Oct 1, 2:48 pm, Joy wrote:
I have 2 colums of numbers and I need to find out this criteria. Do I use
the If function?

A129 and B117 = 1
A129 and B1<17 = 2
A1<29 and B117 = 3
A1<29 and B1<17 = 4
A1=29 and B1=17 = 5
--
Thanks, Joy

more than 4 if functions?
 

You're welcome - thanks for feeding back.

Pete

On Oct 1, 3:52 pm, Joy wrote:
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



"Pete_UK" wrote:
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 B117 and B1<17, and A129 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(A129,IF(B117,1,2),IF(B117,3,4)))

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

Hope this helps.

Pete

On Oct 1, 2:48 pm, Joy wrote:
I have 2 colums of numbers and I need to find out this criteria. Do I use
the If function?

A129 and B117 = 1
A129 and B1<17 = 2
A1<29 and B117 = 3
A1<29 and B1<17 = 4
A1=29 and B1=17 = 5
--
Thanks, Joy- Hide quoted text -

- Show quoted text -

more than 4 if functions?
 

On Oct 1, 6:48 am, Joy wrote:
I have 2 colums of numbers and I need to find out this criteria.
Do I use the If function?

You could. Alternatively....

A129 and B117 = 1
A129 and B1<17 = 2
A1<29 and B117 = 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:

a29,b17: 1
a29,b<17: 2
a<29,b17: 3
a<29,b<17: 4
a=29,b=17: 5
a=29,b17: 5
a29,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 A29 (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.)