# 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)))))),"")

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))))),"")))

3. ## Re: multiple "IF" formula

Thanks but it didn't work!

4. ## Re: multiple "IF" formula

Did the formula get accepted? How did it not work?

5. ## Re: multiple "IF" formula

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

6. ## Re: multiple "IF" formula

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

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

8. ## Re: multiple "IF" formula

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

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!!???

10. ## Re: multiple "IF" formula

Hi everyone

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

11. ## Re: multiple "IF" formula

You are going to have to elaborate on that....

12. ## Re: multiple "IF" formula

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

13. ## Re: multiple "IF" formula

Which is specifically?

14. ## Re: multiple "IF" formula

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

15. ## Re: multiple "IF" formula

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

16. ## Re: multiple "IF" formula

i think he means like makeing x = 3

which you could do with named ranges

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

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

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.

20. ## Re: multiple "IF" formula

Thank you guys

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1