I am trying to build an "IF" formula with a lot of parameters. e.g.
=IF(A1="CAT",12.5,IF(A1="DOG", 50.6,IF(... Just continue that on
for most of the animals out there to see the size I'm going for. I can get
up to about 7 entries and I get the message "The specified formula cannot be
entered because it uses more levels of nesting than are allowed in the
current file format". I don't know if I need to split the formula some how
or just what. If possible I do need these in the same function. Any help or
ideas/suggestions would deeply appreciated. Thank you.

Set up a lookup table...

See this:

http://contextures.com/xlFunctions02.html

--
Biff
Microsoft Excel MVP


"Onyx_jh" wrote in message
...
I am trying to build an "IF" formula with a lot of parameters. e.g.
=IF(A1="CAT",12.5,IF(A1="DOG", 50.6,IF(... Just continue that
on
for most of the animals out there to see the size I'm going for. I can
get
up to about 7 entries and I get the message "The specified formula cannot
be
entered because it uses more levels of nesting than are allowed in the
current file format". I don't know if I need to split the formula some
how
or just what. If possible I do need these in the same function. Any help
or
ideas/suggestions would deeply appreciated. Thank you.

Build a lookup table. On another sheet (Sheet3 in my example), enter the list
of animals in column A and the value to return for each next to it in column
B. Then, on the sheet where you are specifying an animal in A1, enter this
formula in the cell where you want the value returned:

=IF(ISERROR(VLOOKUP(A1,Sheet3!A:B,2,FALSE)),"Not
found",VLOOKUP(A1,Sheet3!A:B,2,FALSE))

This formula is using Vlookup to find the A1 animal in the table on Sheet3
and return the value next to it. I have wrapped the Vlookup in an IF(ISERROR(
construction so if the animal is not found on Sheet3, "Not found" is returned
instead of a #N/A error. Change Sheet3 to whatever sheet name you use, and
change "Not found" to some other text (or a number) if desired. When you have
new animals, you just add them to the lookup table on Sheet3.

Hope this helps,

Hutch

"Onyx_jh" wrote:

I am trying to build an "IF" formula with a lot of parameters. e.g.
=IF(A1="CAT",12.5,IF(A1="DOG", 50.6,IF(... Just continue that on
for most of the animals out there to see the size I'm going for. I can get
up to about 7 entries and I get the message "The specified formula cannot be
entered because it uses more levels of nesting than are allowed in the
current file format". I don't know if I need to split the formula some how
or just what. If possible I do need these in the same function. Any help or
ideas/suggestions would deeply appreciated. Thank you.

You could create a list of animals with corresponding numbers, such as in A1
Cat, B1 12,5, A2 Dog, B2 50,6. Carry on down as far as required, then name
this list say Animals

Now where you want these numbers to appear, use a VLOOKUP statement.

Say you have Dog in Sheet2! A12, and you want the corresponding value to
show in E12, then in E12 enter
=IF(A12="","",VLOOKUP(A12,Animals,2,0))
--
HTH

Kassie

Replace xxx with hotmail


"Onyx_jh" wrote:

I am trying to build an "IF" formula with a lot of parameters. e.g.
=IF(A1="CAT",12.5,IF(A1="DOG", 50.6,IF(... Just continue that on
for most of the animals out there to see the size I'm going for. I can get
up to about 7 entries and I get the message "The specified formula cannot be
entered because it uses more levels of nesting than are allowed in the
current file format". I don't know if I need to split the formula some how
or just what. If possible I do need these in the same function. Any help or
ideas/suggestions would deeply appreciated. Thank you.

Put all those conditions in cells, then use VLOOKUP. Example
K L
1 CAT 12.5
2 DOG 50.6
3 BIRD 22.7

and use a formula like =VLOOKUP(A1,K1:L100,2,FALSE)

Bob Umlas
Excel MVP

"Onyx_jh" wrote in message
...
I am trying to build an "IF" formula with a lot of parameters. e.g.
=IF(A1="CAT",12.5,IF(A1="DOG", 50.6,IF(... Just continue that
on
for most of the animals out there to see the size I'm going for. I can
get
up to about 7 entries and I get the message "The specified formula cannot
be
entered because it uses more levels of nesting than are allowed in the
current file format". I don't know if I need to split the formula some
how
or just what. If possible I do need these in the same function. Any help
or
ideas/suggestions would deeply appreciated. Thank you.

That works awesome and perfect. Thank you all.

"Tom Hutchins" wrote:

Build a lookup table. On another sheet (Sheet3 in my example), enter the list
of animals in column A and the value to return for each next to it in column
B. Then, on the sheet where you are specifying an animal in A1, enter this
formula in the cell where you want the value returned:

=IF(ISERROR(VLOOKUP(A1,Sheet3!A:B,2,FALSE)),"Not
found",VLOOKUP(A1,Sheet3!A:B,2,FALSE))

This formula is using Vlookup to find the A1 animal in the table on Sheet3
and return the value next to it. I have wrapped the Vlookup in an IF(ISERROR(
construction so if the animal is not found on Sheet3, "Not found" is returned
instead of a #N/A error. Change Sheet3 to whatever sheet name you use, and
change "Not found" to some other text (or a number) if desired. When you have
new animals, you just add them to the lookup table on Sheet3.

Hope this helps,

Hutch

"Onyx_jh" wrote:

I am trying to build an "IF" formula with a lot of parameters. e.g.
=IF(A1="CAT",12.5,IF(A1="DOG", 50.6,IF(... Just continue that on
for most of the animals out there to see the size I'm going for. I can get
up to about 7 entries and I get the message "The specified formula cannot be
entered because it uses more levels of nesting than are allowed in the
current file format". I don't know if I need to split the formula some how
or just what. If possible I do need these in the same function. Any help or
ideas/suggestions would deeply appreciated. Thank you.