nested formula help needed

[donkey26]

This is what the help menu tells me.

"Nest no more than seven functions You can enter, or nest, no more
than seven levels of functions within a function. Learn about creating
nested functions."

I was curious if anyone could tell me how to write a IF statement with
more than seven functions? Is it even possible? Is there a way to
combine functions to give the same effect? I am trying to write one
with approximately 25. Any help would be greatly appreciated. I have
tried just about everything and am very stumped.

Thanks.

 

[Peo Sjoblom]

Post your data, most can be accomplished using some form of lookup function,
there are also if workarounds using the ampersand or name smaller portions
of formula and then use a master formula. The ampersand version looks like
this

=IF(A1="a",10,"")&IF(A1="b",15,"")&IF(A1="c",20,"")&and so on

the equivalent vlookup would be

=VLOOKUP(A1,{"a",10;"b",15;"c",20 and so on},2,0)

I always use a lookup since when the [if ampersand] gets really bit it is
really annoying to audit..

 

[Ron Rosenfeld]

I was curious if anyone could tell me how to write a IF statement with
more than seven functions? Is it even possible? Is there a way to
combine functions to give the same effect? I am trying to write one
with approximately 25. Any help would be greatly appreciated. I have
tried just about everything and am very stumped.

Well you cannot nest more than seven functions.

With regard to IF statements, there is usually a better way to do it,
frequently involving lookup tables (VLOOKUP or HLOOKUP); sometimes MATCH and
INDEX.

The reason the lookup tables are usually better is because they are usually
easier to understand, and easier to modify.

IF you post some more specific information, I'm sure someone will be able to
help.


--ron

 

[Guest]

I'm having trouble getting this to work. Here is what I want to do. I need to calculate a person's percentage increase by month. Each month has a different percentage increase. My formula needs to say if(A3=1,8.33%)ifA3=2,12.00%) and so on til I get to 12, however, I can only enter in 7. How would the lookup work in this case?

 

[JMay]

Check out the choose() function in help.
two arguments, first one - your value in A3 ( 1,2,3,4 up to 12)
second argument, a series of values (in your case it would be
0.0833, 0.12, 0.15 etc) note each is separated by the comma character.
That should work well for you.
HTH

I'm having trouble getting this to work. Here is what I want to do. I

need to calculate a person's percentage increase by month. Each month has a
different percentage increase. My formula needs to say
if(A3=1,8.33%)ifA3=2,12.00%) and so on til I get to 12, however, I can only
enter in 7. How would the lookup work in this case?

 

[Norman Harker]

Hi Gadk!

Try VLOOKUP

=VLOOKUP(A3,$H$1:$I$12,2,FALSE)

H1:I12 contains your alternatives with
H1: 1 I1 8.33%
H2:2 I2 12.00%
etc

The final argument in the function requires an exact match of the item
in A3 with the table.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

I'm having trouble getting this to work. Here is what I want to do.

I need to calculate a person's percentage increase by month. Each
month has a different percentage increase. My formula needs to say
if(A3=1,8.33%)ifA3=2,12.00%) and so on til I get to 12, however, I can
only enter in 7. How would the lookup work in this case?