maximum characters?

[B G]

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

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

[B G]

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

[[email protected]]

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",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 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!

[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




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

[B G]

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

[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


"B G" <[email protected]> wrote in message
news:[email protected]...
>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