Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula/functions for average and if functions | Excel Worksheet Functions | |||
XL2003 FILTER FUNCTIONS VS. XL2007 FILTER FUNCTIONS | Excel Worksheet Functions | |||
efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
Looking for a site with functions that substitute the ATP functions | Excel Worksheet Functions | |||
Nesting functions in the functions dialog box | Excel Worksheet Functions |