Function Arguments Jessica 15.09.2006 05:24:01 Is there any way of having more than one scenario in an arguement so that the "TRUE" answer will depend on more than one "IF"

RE: Function Arguments Jim Thomlinson 15.09.2006 06:27:02 Not too srue what you are driving at here. You can use the logical And / Or functions =if(and(A1 = 1, B1 = 1), true, False) Which can be simplified to =and(A1 = 1, B1 = 1) there is also an or function =or(a1 = 1, B1 = 1) finally you could use nested if functions this mimics the above or function =if(A1 = 1, true, if(b1 = 1, true, false) And this mimics the and function =if(A1 = 1, if(B1 = ,true, false), false) -- HTH... Jim Thomlinson "Jessica" wrote: [Quoted Text] > Is there any way of having more than one scenario in an arguement so that the > "TRUE" answer will depend on more than one "IF"

Re: Function Arguments "Roger Govier" <roger[ at ]technologyNOSPAM4u.co.uk> 15.09.2006 07:21:58 Hi Jessica The answer is Yes. You can nest up to 7 levels of IF statement in a single test. There are also methods of overcoming this limit, and other better methods if your requirement approaches or exceeds 7. I many circumstances, the use of AND or OR will provide your result, but you can also combine IF with AND or with OR statements. An example of 4 levels of nesting would be =IF(A1<4,TRUE,IF(A1>9,TRUE,IF(B1<5,TRUE,IF(B1>14,TRUE,FALSE)))) would return True if any of the 4 conditions are met. This could also be written (more simply) with only one level of nesting as =IF(OR(A1<4,A1>9,B1<5,B1>14),TRUE,FALSE) or even more simply as =OR(A1<4,A1>9,B1<5,B1>14) which does not use IF at all and returns True or False as its result Alternatively, if all 4 conditions had to be met to return True, then again, without any IF's =AND(A1>3,A1<10,B1>5,B1<15) Clearly if your answer needs to be something other than True or False, then an IF statement needs to be included =IF(A1>90,"Excellent",IF(A1>70,"Very Good",IF(A1>50,"Good","Try Harder"))) It all depends what you are trying to achieve as to which method you use I hope this helps -- Regards Roger Govier "Jessica" <Jessica[ at ]discussions.microsoft.com> wrote in message news:62B37088-A5BC-488E-916F-D5C4A4B14FE9[ at ]microsoft.com... [Quoted Text] > Is there any way of having more than one scenario in an arguement so > that the > "TRUE" answer will depend on more than one "IF"

Re: Function Arguments Jessica 17.09.2006 23:51:01 Thank you, im trying to create a sheet which will automatically identify points allocated to a person depending on what position they came in a race. There are up to 25 competitors at one time and points for all places. How would i get around the 7 limit? "Roger Govier" wrote: [Quoted Text] > Hi Jessica > > The answer is Yes. You can nest up to 7 levels of IF statement in a > single test. > There are also methods of overcoming this limit, and other better > methods if your requirement approaches or exceeds 7. > I many circumstances, the use of AND or OR will provide your result, but > you can also combine IF with AND or with OR statements. > > An example of 4 levels of nesting would be > =IF(A1<4,TRUE,IF(A1>9,TRUE,IF(B1<5,TRUE,IF(B1>14,TRUE,FALSE)))) > would return True if any of the 4 conditions are met. > > This could also be written (more simply) with only one level of nesting > as > =IF(OR(A1<4,A1>9,B1<5,B1>14),TRUE,FALSE) > or even more simply as > =OR(A1<4,A1>9,B1<5,B1>14) > which does not use IF at all and returns True or False as its result > > Alternatively, if all 4 conditions had to be met to return True, then > again, without any IF's > =AND(A1>3,A1<10,B1>5,B1<15) > > Clearly if your answer needs to be something other than True or False, > then an IF statement needs to be included > =IF(A1>90,"Excellent",IF(A1>70,"Very Good",IF(A1>50,"Good","Try > Harder"))) > > It all depends what you are trying to achieve as to which method you use > > I hope this helps > > -- > Regards > > Roger Govier > > > "Jessica" <Jessica[ at ]discussions.microsoft.com> wrote in message > news:62B37088-A5BC-488E-916F-D5C4A4B14FE9[ at ]microsoft.com... > > Is there any way of having more than one scenario in an arguement so > > that the > > "TRUE" answer will depend on more than one "IF" > > >

Re: Function Arguments Gord Dibben <gorddibbATshawDOTca> 18.09.2006 02:05:26 Most likely a Lookup table and a VLOOKUP formula. On Sheet2 enter your places numbers 1 through 25 In column B enter the points for each corresponding placing. Back to Sheet 1 In Column A you would have the competitor names. I column B you would have their placing in the race. In C1 enter =VLOOKUP(B1,Sheet2!$A$1:$B$25,2,FALSE) Drag/copy down to get points in column C. Gord Dibben MS Excel MVP On Sun, 17 Sep 2006 16:51:01 -0700, Jessica <Jessica[ at ]discussions.microsoft.com> wrote: [Quoted Text] >Thank you, im trying to create a sheet which will automatically identify >points allocated to a person depending on what position they came in a race. >There are up to 25 competitors at one time and points for all places. How >would i get around the 7 limit? > >"Roger Govier" wrote: > >> Hi Jessica >> >> The answer is Yes. You can nest up to 7 levels of IF statement in a >> single test. >> There are also methods of overcoming this limit, and other better >> methods if your requirement approaches or exceeds 7. >> I many circumstances, the use of AND or OR will provide your result, but >> you can also combine IF with AND or with OR statements. >> >> An example of 4 levels of nesting would be >> =IF(A1<4,TRUE,IF(A1>9,TRUE,IF(B1<5,TRUE,IF(B1>14,TRUE,FALSE)))) >> would return True if any of the 4 conditions are met. >> >> This could also be written (more simply) with only one level of nesting >> as >> =IF(OR(A1<4,A1>9,B1<5,B1>14),TRUE,FALSE) >> or even more simply as >> =OR(A1<4,A1>9,B1<5,B1>14) >> which does not use IF at all and returns True or False as its result >> >> Alternatively, if all 4 conditions had to be met to return True, then >> again, without any IF's >> =AND(A1>3,A1<10,B1>5,B1<15) >> >> Clearly if your answer needs to be something other than True or False, >> then an IF statement needs to be included >> =IF(A1>90,"Excellent",IF(A1>70,"Very Good",IF(A1>50,"Good","Try >> Harder"))) >> >> It all depends what you are trying to achieve as to which method you use >> >> I hope this helps >> >> -- >> Regards >> >> Roger Govier >> >> >> "Jessica" <Jessica[ at ]discussions.microsoft.com> wrote in message >> news:62B37088-A5BC-488E-916F-D5C4A4B14FE9[ at ]microsoft.com... >> > Is there any way of having more than one scenario in an arguement so >> > that the >> > "TRUE" answer will depend on more than one "IF" >> >> >>