maximum characters?
 

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",Inp ut!B6*C13*C9,IF(C11="FW",Input!B7*C13*C9,IF(C11="H I",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

maximum characters?
 

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

B G wrote:
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",Inp ut!B6*C13*C9,IF(C11="FW",Input!B7*C13*C9,IF(C11="H I",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

maximum characters?
 

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


"shail" wrote:

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

B G wrote:
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",Inp ut!B6*C13*C9,IF(C11="FW",Input!B7*C13*C9,IF(C11="H I",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

maximum characters?
 

B G wrote:
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",Inp ut!B6*C13*C9,IF(C11="FW",Input!B7*C13*C9,IF(C11="H I",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

maximum characters?
 

You can actually nest many more than 7 if statements if you use named
formulas. The first thing you have to do is take your current formula
and copy it from the formula bar. Go to Insert\Name\Define and type in
a formula name (I used IFOne) and then paste in your formula in the
Refers to: box replacing the cell reference and then click OK. Once
that is done, create the formula for all the other IF scenarios that
you have and name them in succession (i.e. IFTwo, IFThree etc.) like
you did above. Then in the cell you want the formulas to appear enter
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!


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=565179

maximum characters?
 

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




B G wrote:
I am sorry but I do not understand what you are saying. Could you please
elaborate? (Sorry I am not a very experienced user)
--
Barb


"shail" wrote:

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

B G wrote:
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",Inp ut!B6*C13*C9,IF(C11="FW",Input!B7*C13*C9,IF(C11="H I",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

maximum characters?
 

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



"Excelenator" wrote:


You can actually nest many more than 7 if statements if you use named
formulas. The first thing you have to do is take your current formula
and copy it from the formula bar. Go to Insert\Name\Define and type in
a formula name (I used IFOne) and then paste in your formula in the
Refers to: box replacing the cell reference and then click OK. Once
that is done, create the formula for all the other IF scenarios that
you have and name them in succession (i.e. IFTwo, IFThree etc.) like
you did above. Then in the cell you want the formulas to appear enter
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!


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=565179

maximum characters?
 

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,Inpu t!B4*C13*C9,Input!B5*C13*C9,Input!B6*C13*C9,Input! B7*C13*C9,Input!B8*C13*C9,Input!B9*C13*C9,Input!B1 0*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


"B G" wrote in message
...
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",Inp ut!B6*C13*C9,IF(C11="FW",Input!B7*C13*C9,IF(C11="H I",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