J
Jessica
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"
"TRUE" answer will depend on more than one "IF"
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
Jim Thomlinson
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)
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)
R
Roger Govier
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
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
J
Jessica
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?
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?
G
Gord Dibben
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 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