Mutiple IF conditions
Posted by Oli on December 20, 2001 10:26 AM
I need help with my I.T coursework!
How can i have multiple "IF" conditions?
This is the formula i have so far:
=IF(C4=1,'C:\oli\I.T\Wages\[employees.xls]Sheet1'!$C$4,"")
However i also want to include the formula:
=IF(C4=2,'C:\oli\I.T\Wages\[employees.xls]Sheet1'!$C$5,"")
and many more If conditions in the same cell, ideally it would be best if i could create some kind of a relationship between cell "C4" and what is displayed in cell C3, there is a relationship of +3 between what cell C4 equals and what i want to display in cell C3.
If there is no way to make a formula to do this i could still put in loads of IF conditions however i do not know how to do more than 1 in the same cell :(
Posted by Joe Was on December 20, 2001 11:09 AM
You can only nest seven levels of IF statments, but you can reference a cell that includes additional IF statments to extend this limit.
The IF statement has the syntax: Result=IF(test,Then,Else)
So, with this in mind you can replace the "Else" part with a new "IF" up to seven times. You can also mix functions within your IF statements, like:
=IF(AND(I5>0,I5<5),1, IF(AND(I5>4,I5<9),2, IF(AND(I5>8,I5<13), 3, IF(AND(I5>12,I5<17), 4, IF(AND(I5>16,I5<21), 5, IF(AND(I5>20,I5<25),6,0))))))
Note: AND(I5>0,I5<5) is: test1.
,1, is: Then 1.
IF(And(I5>4,I5<9) is: test2.
,2, is: Then 2.
...
If(AND(I5>20,I5<25) is test6.
,6, is: Then 6. and
,0)))))) is: the last Else.
This groups up to 24 collections into groups of no more than 4. With nested IF statements the trick is the "()" parentheses order. JSW
Posted by Tom Dickinson on December 20, 2001 11:14 AM
If its not cheating to give assitance:
Multiple (or nesting) if/then is accomplished by putting the 2nd if/then in the "else" part of the 1st if/then. Example:
If1(Condition1,Then1,If2(Condition2,Then2,Else2))
If(A=1,"Yes",If(B=1,"No","Maybe"))
For your quandry, rather than nesting, just concatenate:
='C:\oli\I.T\Wages\[employees.xls]Sheet1'!C" & C4 + 3)
Posted by Oli on December 20, 2001 11:50 AM
the formula:
='C:\oli\I.T\Wages\[employees.xls]Sheet1'!C" & C4 + 3)
that you gave me doesnt work!
i used it in the context:
='IF(C4=1,C:\oli\I.T\Wages\[employees.xls]Sheet1'!C" & C4 + 3,"")
Is this the correct way to do it?
I am not sure you have the correct syntax here as i think excel is trying to add 3 to the value of cell c4 in the other sheet, whereas infact i want to add +3 to the cell reference!
Posted by Juan Pablo G. on December 20, 2001 1:36 PM
Is this what you want ?
=INDIRECT("'C:\oli\I.T\Wages\[employees.xls]Sheet1'!C" & ROW() + 3)
Juan Pablo G.
Posted by Tom. Dickinson on December 20, 2001 7:28 PM
Ok. 2 methods.
The one from Juan, only change "row()" to "C4" os it looks like this:
=INDIRECT("'C:\oli\I.T\Wages\[employees.xls]Sheet1'!C" & C4 + 3)
The other metod is
=OFFSET("'C:\oli\I.T\Wages\[employees.xls]Sheet1'!C3", C4, 0)
Sorry for misleading you earlier, that was the programing method.
Posted by Oli on December 21, 2001 7:24 AM
No, that aint it, i want it to be cell C4 (a numerical ID number)+ 3 = the row number of the data i want!
This data is then displayed in cell C5
see my original post also.
Posted by Oli on December 21, 2001 7:28 AM
THANK YOU TOM!!!
that bit o' code works great!
Thanks a lot for the help you really saved my monitor from a good punching (i was getting a "little" annoyed ;-)
