Function Arguments

[Guest]

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"

 

[Guest]

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)

 

[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

 

[Guest]

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?

 

[Guest]

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?

 

[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

 

[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