# multiple "IF" formula

1. ## multiple "IF" formula

Hi all

I have a multiple "IF" formula as below and excel says: "too many arguments...". What's your solution for this problem?

=IF(C2="X",IF(I2<='Definition-S,P'!A4,1,IF(I2<='Definition-S,P'!A5,2,IF(I2<='Definition-S,P'!A6,3,IF(I2<='Definition-S,P'!A7,4,IF(I2<='Definition-S,P'!A8,5))))),IF(C2="Y",IF(I2<='Definition-S,P'!A13,1,IF(I2<='Definition-S,P'!A14,2,IF(I2<='Definition-S,P'!A15,3,IF(I2<='Definition-S,P'!A16,4,IF(I2<='Definition-S,P'!A17,5)))))),IF(C2="Z",IF(I2<='Definition-S,P'!A22,1,IF(I2<='Definition-S,P'!A23,2,IF(I2<='Definition-S,P'!A24,3,IF(I2<='Definition-S,P'!A25,4,IF(I2<='Definition-S,P'!A26,5)))))),"")  Register To Reply

2. ## Re: multiple "IF" formula

Try:

=IF(C2="X",IF(I2<='Definition-S,P'!A4,1,IF(I2<='Definition-S,P'!A5,2,IF(I2<='Definition-S,P'!A6,3,IF(I2<='Definition-S,P'!A7,4,IF(I2<='Definition-S,P'!A8,5))))),IF(C2="Y",IF(I2<='Definition-S,P'!A13,1,IF(I2<='Definition-S,P'!A14,2,IF(I2<='Definition-S,P'!A15,3,IF(I2<='Definition-S,P'!A16,4,IF(I2<='Definition-S,P'!A17,5))))),IF(C2="Z",IF(I2<='Definition-S,P'!A22,1,IF(I2<='Definition-S,P'!A23,2,IF(I2<='Definition-S,P'!A24,3,IF(I2<='Definition-S,P'!A25,4,IF(I2<='Definition-S,P'!A26,5))))),"")))  Register To Reply

3. ## Re: multiple "IF" formula

Thanks but it didn't work!   Register To Reply

4. ## Re: multiple "IF" formula

Did the formula get accepted? How did it not work?  Register To Reply

5. ## Re: multiple "IF" formula

No, the same note! "you've entered too many arguments for this function"  Register To Reply

6. ## Re: multiple "IF" formula

Try again. It works for me in 2007 and even in 2003.  Register To Reply

7. ## Re: multiple "IF" formula

It did I guess!!! First I thought you had changed the last parentheses but now I copied the whole formula in it and it worked!!
I will perform it for the whole sheet then let you know about the final result
Thanks a lot   Register To Reply

8. ## Re: multiple "IF" formula

=MATCH(I2;OFFSET('Definition-S,P'!A4:A8;9*CODE(UPPER(C2))-792 )  Register To Reply

9. ## Re: multiple "IF" formula

I tested the formula but it didn't work for items "Y" and "Z"! I can't realize what the problem is!!???  Register To Reply

10. ## Re: multiple "IF" formula

Hi everyone

How can we fix some variables in a formula and then extend it to the other cells?  Register To Reply

11. ## Re: multiple "IF" formula

You are going to have to elaborate on that....  Register To Reply

12. ## Re: multiple "IF" formula

I solved my mistake on the former problem. But please answer the latter one  Register To Reply

13. ## Re: multiple "IF" formula

Which is specifically?  Register To Reply

14. ## Re: multiple "IF" formula

I mean fixing the variables in different formulas (just some of them). Is it clear?  Register To Reply

15. ## Re: multiple "IF" formula

It's not clear. What exactly do you mean? Give examples too.  Register To Reply

16. ## Re: multiple "IF" formula

i think he means like makeing x = 3

which you could do with named ranges  Register To Reply

17. ## Re: multiple "IF" formula

For example in the discussed formula, I wanna keep !A5-!A26 fixed and copy it into the other cells so that only the rest of variables (C*, I*) change in number.

=IF(C2="X",IF(I2<='Definition-S,P'!A4,1,IF(I2<='Definition-S,P'!A5,2,IF(I2<='Definition-S,P'!A6,3,IF(I2<='Definition-S,P'!A7,4,IF(I2<='Definition-S,P'!A8,5))))),IF(C2="Y",IF(I2<='Definition-S,P'!A13,1,IF(I2<='Definition-S,P'!A14,2,IF(I2<='Definition-S,P'!A15,3,IF(I2<='Definition-S,P'!A16,4,IF(I2<='Definition-S,P'!A17,5))))),IF(C2="Z",IF(I2<='Definition-S,P'!A22,1,IF(I2<='Definition-S,P'!A23,2,IF(I2<='Definition-S,P'!A24,3,IF(I2<='Definition-S,P'!A25,4,IF(I2<='Definition-S,P'!A26,5))))),"")))
Hope it is clear enough
Thanks  Register To Reply

18. ## Re: multiple "IF" formula

That is called Absolute referencing...

put \$ in front of row/column indicator that you want to freeze, e.g. \$A\$5 will freeze cell A5, \$A5 will only freeze column A, A\$5 will only freeze row 5.

Here's more on the subject: http://www.cpearson.com/excel/relative.aspx  Register To Reply

19. ## Re: multiple "IF" formula

\$A\$5 and put the dollar signs in each of the lines you want fixed. The rest will fill down accordingly.  Register To Reply

20. ## Re: multiple "IF" formula

Thank you guys
Your comments was very very helpful ))  Register To Reply