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

"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

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

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

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

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

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

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

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

"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.