Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Logical Functions
I must admit. I am horrible at logical functions. I am trying to compose a
formula that will return a value of "1" if number is greater than 1, less than 4, greater than 949, and less than 991. Here is what I have now but it is not working: =IF(AND(G70,G7<4,G7949,G7<991),1,0) Any help would be awesome! -- -CRM |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Logical Functions
You text says: **if number is greater than 1** but your formula saud greater
than zero. Assuming that you mean = 1 and <= the other numbers, try =IF(OR(AND(G7=1,G7<=4),AND(G7=949,G7<=991)),1,0) Note that if you just want zero or 1 then: =--OR(AND(G7=1,G7<=4),AND(G7=949,G7<=991)) Will do that. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "saltnsnails" wrote in message ... I must admit. I am horrible at logical functions. I am trying to compose a formula that will return a value of "1" if number is greater than 1, less than 4, greater than 949, and less than 991. Here is what I have now but it is not working: =IF(AND(G70,G7<4,G7949,G7<991),1,0) Any help would be awesome! -- -CRM |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Logical Functions
If you look at your IF statement you are saying G7 must be greater than 0
AND G7 must be less than 4 AND G7 must be greater than 949 AND G7 must be less than 991. All of those conditions must be met for your formula to return 1. G7 cannot be less than 4 and greater than 949 at the same time. What you want can be done with =IF(OR(AND(G70,G7<4),AND(G7949,G7<991)),1,0) or =IF(OR(AND(G71,G7<4),AND(G7949,G7<991)),1,0) You say the number must be greater than 1 but your formula says 0. So if the number must be greater than 0, use the first formula, greater than 1, use the second. Tyro "saltnsnails" wrote in message ... I must admit. I am horrible at logical functions. I am trying to compose a formula that will return a value of "1" if number is greater than 1, less than 4, greater than 949, and less than 991. Here is what I have now but it is not working: =IF(AND(G70,G7<4,G7949,G7<991),1,0) Any help would be awesome! -- -CRM |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Logical Functions
On Tue, 19 Feb 2008 08:28:00 -0800, saltnsnails
wrote: I must admit. I am horrible at logical functions. I am trying to compose a formula that will return a value of "1" if number is greater than 1, less than 4, greater than 949, and less than 991. Here is what I have now but it is not working: =IF(AND(G70,G7<4,G7949,G7<991),1,0) Any help would be awesome! You need to express what you want more clearly -- then you might find logical functions easier. You don't say if you want the number to meet all of the criteria, or any of the criteria, or perhaps some pairs of the criteria. There is no way a number can meet all those criteria simultaneously. So if that's what you want, the formula is: 0 Also, your statement and formula disagree as to what you want to do with regard to the number 1. Your statement is "number is greater than 1", whereas your formula reads G70. If you want a 1 if the number meets any one of those criteria, then you need the OR function which will return a TRUE/FALSE if the number is: greater than 1 OR less than 4 OR greater than 949 OR less than 991 In other words, if the number meets and one of the criteria. =OR(n1,n<4,n949,n<991) IF you want a 1 or 0, you can just convert TRUE/FALSE to a number: =--OR(n1,n<4,n949,n<991) or use an IF statement: =IF(OR(n1,n<4,n949,n<991),1,0) IF you want something else, you need to be clear. And I believe that being clear in your own head, will help with your difficulties with logical functions. --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Logical Functions
It is's less than 4 it can't also be greater than 949, so you'll never
satisfy the condition. What are you really trying to do? -- David Biddulph "saltnsnails" wrote in message ... I must admit. I am horrible at logical functions. I am trying to compose a formula that will return a value of "1" if number is greater than 1, less than 4, greater than 949, and less than 991. Here is what I have now but it is not working: =IF(AND(G70,G7<4,G7949,G7<991),1,0) Any help would be awesome! -- -CRM |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Logical Functions
And of course if you look at Ron's =OR(n1,n<4,n949,n<991) you'll realise
that if you've tested for n1, there's no point in checking for n949, and similarly if you've tested for n<991 there's no point in testing for n<4, hence you can simplify to =OR(n1,n<991), but then look further so if you don't satisfy n1 you must satisfy n<=1 and must therefore satisfy n<991, so the answer to the OR function is always TRUE. Hence, as Ron says, you need to think first about what it is that you're trying to do. When you've sorted out precisely what the question is, getting Excel to answer it is usually the easy part. -- David Biddulph "Ron Rosenfeld" wrote in message ... You need to express what you want more clearly -- then you might find logical functions easier. You don't say if you want the number to meet all of the criteria, or any of the criteria, or perhaps some pairs of the criteria. There is no way a number can meet all those criteria simultaneously. So if that's what you want, the formula is: 0 Also, your statement and formula disagree as to what you want to do with regard to the number 1. Your statement is "number is greater than 1", whereas your formula reads G70. If you want a 1 if the number meets any one of those criteria, then you need the OR function which will return a TRUE/FALSE if the number is: greater than 1 OR less than 4 OR greater than 949 OR less than 991 In other words, if the number meets and one of the criteria. =OR(n1,n<4,n949,n<991) IF you want a 1 or 0, you can just convert TRUE/FALSE to a number: =--OR(n1,n<4,n949,n<991) or use an IF statement: =IF(OR(n1,n<4,n949,n<991),1,0) IF you want something else, you need to be clear. And I believe that being clear in your own head, will help with your difficulties with logical functions. On Tue, 19 Feb 2008 08:28:00 -0800, saltnsnails wrote: I must admit. I am horrible at logical functions. I am trying to compose a formula that will return a value of "1" if number is greater than 1, less than 4, greater than 949, and less than 991. Here is what I have now but it is not working: =IF(AND(G70,G7<4,G7949,G7<991),1,0) Any help would be awesome! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Logical Functions | Excel Worksheet Functions | |||
Logical Functions | Excel Worksheet Functions | |||
logical functions | Excel Worksheet Functions | |||
using logical functions | Excel Worksheet Functions | |||
logical functions | Excel Worksheet Functions |