maximum characters?

[Guest]

I need help with this formula -

I actually need to add two or more "IF" statements - but it won't let me.
What do I need to do?
--

=IF(C11="BL",Input!B3*C13*C9,IF(C11="BR",Input!B4*C13*C9,IF(C11="EP",Input!B5*C13*C9,IF(C11="ER",Input!B6*C13*C9,IF(C11="FW",Input!B7*C13*C9,IF(C11="HI",Input!B8*C13*C9,IF(C11="NE",Input!B9*C13*C9,IF(C11="PB",Input!B8*C13*C9))))))))

When I go to put another "IF" statement in - it highlights it and says I
have an error. I'm assuming this is because I can only use 7 if statements
within a forumula. What other option do I have?

Thanks

 

[shail]

Hi BG,

Yes you are right, it is because you cannot use more than 7 if
statements

If you want to put more than 7 if statements then -

1. Name the cell where you have 7 if statements (say at B3 and named
"oldone")
2. Put down the next if statements in the other cell (say at C3 and
named "newone")
3. Put down the if statement at D3 as

=if(oldone,oldone,newone)

Hope this will work for you


Thanks

Shail

 

[Guest]

I am sorry but I do not understand what you are saying. Could you please
elaborate? (Sorry I am not a very experienced user)

 

[bplumhoff]

I need help with this formula -

I actually need to add two or more "IF" statements - but it won't let me.
What do I need to do?
--

=IF(C11="BL",Input!B3*C13*C9,IF(C11="BR",Input!B4*C13*C9,IF(C11="EP",Input!B5*C13*C9,IF(C11="ER",Input!B6*C13*C9,IF(C11="FW",Input!B7*C13*C9,IF(C11="HI",Input!B8*C13*C9,IF(C11="NE",Input!B9*C13*C9,IF(C11="PB",Input!B8*C13*C9))))))))

When I go to put another "IF" statement in - it highlights it and says I
have an error. I'm assuming this is because I can only use 7 if statements
within a forumula. What other option do I have?

Thanks

Hello,

=choose(match(c11,{"BL";"BR";"EP"},),Input!B3*C13*C9,Input!B4*C13*C9,Input!B5*C13*C9)

Regards,
Bernd

 

[Excelenator]

You can actually nest many more than 7 if statements if you use name
formulas. The first thing you have to do is take your current formul
and copy it from the formula bar. Go to Insert\Name\Define and type i
a formula name (I used IFOne) and then paste in your formula in th
Refers to: box replacing the cell reference and then click OK. Onc
that is done, create the formula for all the other IF scenarios tha
you have and name them in succession (i.e. IFTwo, IFThree etc.) lik
you did above. Then in the cell you want the formulas to appear ente
the following IF statement:

IF(IFOne,IFOne,IF(IFTwo,IFTwo,IF(IFThree,IFThree,0)))

The example above will allow you to nest 21 if statements

 

[shail]

Hi BG,

Excelenator had told you the trick in a better way and I hope now you
know how to do it.

Thanks to Excelenator too.

Shail

 

[Guest]

I am not having much luck with that - I'm wondering if it would be easier to
do something like this

4 of the Cells in column B are = to $34, could I do a formula that basically
says:

If cell C11=BR or FW or NE or HI, then multiply cell B3*C13*C9.

This would elimiate the need for more than 7 IF statements, as 4 of the 10
would be included in the statement above.

I'm getting there, slowly - thanks for your help.

 

[Beege]

Try this

=CHOOSE(LOOKUP(C11,{"BL","BR","EP","ER","FW","HI","NE","PB"},{1,2,3,4,5,6,7,8}),Input!B3*C13*C9,Input!B4*C13*C9,Input!B5*C13*C9,Input!B6*C13*C9,Input!B7*C13*C9,Input!B8*C13*C9,Input!B9*C13*C9,Input!B10*C13*C9)

Make sure you take out any line feeds from cut/paste if you do so.
I looks in C11 (LOOKUP) to find the two letters you're looking for, then
chooses (CHOOSE) the calcuation based on what it finds.
HTH

Beege