Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IF Formula
Hi,
I am having problems using IF in a formula. This one works: = IF(C5="01","A", IF(C5="02","B", IF(C5="03","C", IF(C5="04","D", IF(C5="05","E", IF(C5="06","F", IF(C5="08","G", IF(C5="09","H" )))))))) This one does not work. I get an error message. = IF(C5="01","A", IF(C5="02","B", IF(C5="03","C", IF(C5="04","D", IF(C5="05","E", IF(C5="06","F", IF(C5="08","G", IF(C5="09","H" IF(C5="11","I", IF(C5="12","J", IF(C5="13","K", IF(C5="14","L", IF(C5="15","M", IF(C5="16","N", )))))))))))))) As you can see, I am just giving more values. Is there a limit for IF to be used ? Thanks in advance for your help |
#2
|
|||
|
|||
"Pepito Grillo" wrote in message ... Hi, I am having problems using IF in a formula. This one works: = IF(C5="01","A", IF(C5="02","B", IF(C5="03","C", IF(C5="04","D", IF(C5="05","E", IF(C5="06","F", IF(C5="08","G", IF(C5="09","H" )))))))) This one does not work. I get an error message. = IF(C5="01","A", IF(C5="02","B", IF(C5="03","C", IF(C5="04","D", IF(C5="05","E", IF(C5="06","F", IF(C5="08","G", IF(C5="09","H" IF(C5="11","I", IF(C5="12","J", IF(C5="13","K", IF(C5="14","L", IF(C5="15","M", IF(C5="16","N", )))))))))))))) As you can see, I am just giving more values. Is there a limit for IF to be used ? Thanks in advance for your help You can't have more than 7 nested IF's in a cell. the LOOKUP function can be used instead of the formula that gives you an error. Have a look at: http://www.techonthenet.com/excel/formulas/lookup.htm /Fredrik |
#3
|
|||
|
|||
Hi Pepito
The limit is 7 nested functions within a formula. Instead of using an IF formula try CHOOSE: =CHOOSE(C5,"a","b","c","d","e","f","g","h","i","j" ,"k","l","m","n") HTH Cordially Pascal "Pepito Grillo" a écrit dans le message de news: ... Hi, I am having problems using IF in a formula. This one works: = IF(C5="01","A", IF(C5="02","B", IF(C5="03","C", IF(C5="04","D", IF(C5="05","E", IF(C5="06","F", IF(C5="08","G", IF(C5="09","H" )))))))) This one does not work. I get an error message. = IF(C5="01","A", IF(C5="02","B", IF(C5="03","C", IF(C5="04","D", IF(C5="05","E", IF(C5="06","F", IF(C5="08","G", IF(C5="09","H" IF(C5="11","I", IF(C5="12","J", IF(C5="13","K", IF(C5="14","L", IF(C5="15","M", IF(C5="16","N", )))))))))))))) As you can see, I am just giving more values. Is there a limit for IF to be used ? Thanks in advance for your help |
#4
|
|||
|
|||
Hi
yes, you can only nest 7 levels, however your formula can be rewritten as =IF(C5,CHAR(64+C5),"") -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Pepito Grillo" wrote in message ... Hi, I am having problems using IF in a formula. This one works: = IF(C5="01","A", IF(C5="02","B", IF(C5="03","C", IF(C5="04","D", IF(C5="05","E", IF(C5="06","F", IF(C5="08","G", IF(C5="09","H" )))))))) This one does not work. I get an error message. = IF(C5="01","A", IF(C5="02","B", IF(C5="03","C", IF(C5="04","D", IF(C5="05","E", IF(C5="06","F", IF(C5="08","G", IF(C5="09","H" IF(C5="11","I", IF(C5="12","J", IF(C5="13","K", IF(C5="14","L", IF(C5="15","M", IF(C5="16","N", )))))))))))))) As you can see, I am just giving more values. Is there a limit for IF to be used ? Thanks in advance for your help |
#5
|
|||
|
|||
Pepito Grillo wrote:
As you can see, I am just giving more values. Is there a limit for IF to be used ? Yes, there is a limit to the depth of an IF statement. For your problem I wouldn't bother with an IF anyhow. Try the following: [ ] = CHAR(CODE("A")-1+C5) Good luck.... Bill |
#6
|
|||
|
|||
Hi,
Thanks for your reply. I want to enter the formula in D5 Could you help me with this please ? Thanks again. "Fredrik Wahlgren" wrote: "Pepito Grillo" wrote in message ... Hi, I am having problems using IF in a formula. This one works: = IF(C5="01","A", IF(C5="02","B", IF(C5="03","C", IF(C5="04","D", IF(C5="05","E", IF(C5="06","F", IF(C5="08","G", IF(C5="09","H" )))))))) This one does not work. I get an error message. = IF(C5="01","A", IF(C5="02","B", IF(C5="03","C", IF(C5="04","D", IF(C5="05","E", IF(C5="06","F", IF(C5="08","G", IF(C5="09","H" IF(C5="11","I", IF(C5="12","J", IF(C5="13","K", IF(C5="14","L", IF(C5="15","M", IF(C5="16","N", )))))))))))))) As you can see, I am just giving more values. Is there a limit for IF to be used ? Thanks in advance for your help You can't have more than 7 nested IF's in a cell. the LOOKUP function can be used instead of the formula that gives you an error. Have a look at: http://www.techonthenet.com/excel/formulas/lookup.htm /Fredrik |
#7
|
|||
|
|||
Hi,
Thanks for your reply. I want to enter the formula in D5 Could you help me with this please ? Thanks again. "Pepito Grillo" wrote: Hi, I am having problems using IF in a formula. This one works: = IF(C5="01","A", IF(C5="02","B", IF(C5="03","C", IF(C5="04","D", IF(C5="05","E", IF(C5="06","F", IF(C5="08","G", IF(C5="09","H" )))))))) This one does not work. I get an error message. = IF(C5="01","A", IF(C5="02","B", IF(C5="03","C", IF(C5="04","D", IF(C5="05","E", IF(C5="06","F", IF(C5="08","G", IF(C5="09","H" IF(C5="11","I", IF(C5="12","J", IF(C5="13","K", IF(C5="14","L", IF(C5="15","M", IF(C5="16","N", )))))))))))))) As you can see, I am just giving more values. Is there a limit for IF to be used ? Thanks in advance for your help |
#8
|
|||
|
|||
Thanks for your reply.
In some cases I would like to get a word or name insted of a letter as result. Something like the following: = IF(C5="01","John", IF(C5="02","Peter", IF(C5="03","Jose", IF(C5="04","Ronald", IF(C5="05","JACK", IF(C5="06","Rene", IF(C5="08","Alex", IF(C5="09","Rob", IF(C5="11","Denis", IF(C5="12","Junior" )))))))))) "Bill Martin -- (Remove NOSPAM from addre" wrote: Pepito Grillo wrote: As you can see, I am just giving more values. Is there a limit for IF to be used ? Yes, there is a limit to the depth of an IF statement. For your problem I wouldn't bother with an IF anyhow. Try the following: [ ] = CHAR(CODE("A")-1+C5) Good luck.... Bill |
#9
|
|||
|
|||
Pepito Grillo wrote:
In some cases I would like to get a word or name insted of a letter as result. Something like the following: = IF(C5="01","John", IF(C5="02","Peter", IF(C5="03","Jose", IF(C5="04","Ronald", If you're looking to translate an arbitrary number of numbers into arbitrary or easily changed text, then I wouldn't mess with IF() statements which can be difficult to figure out or modify later on. I'd create a simple lookup table on a sheet somewhere and use the VLOOKUP statement. 01 John A 02 Peter B 03 Jose C then: Then to get "Peter" your use [ ] = VLOOKUP(2, A1:C3, 2) and to get "C" you use [ ] = VLOOKUP(3,A1:C3,3) That makes it very simple to modify the lookup table and to just glance at it and understand which number produces what result. Good luck.... Bill |
#10
|
|||
|
|||
"Pepito Grillo" wrote:
Hi, Thanks for your reply. I want to enter the formula in D5 Could you help me with this please ? Thanks again. As others have indicated, a VLOOKUP function will be the best for your situation. This site will help teach you how to use the VLOOKUP function: http://www.contextures.com/xlFunctions02.html -- Greg phobos78-marslink-net Replace dashes and move in by 1 planet to reply. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
Problem with VBA returning the contents of a long formula. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |