7+ if statements

I am trying to create a worksheet that will allocate operating expenses to 6
different departments. I have 14 allocation methods on tab "Alloc" that are
identified with letters A thru L. On the expense alloction tab I have
assigend a letter(A thru L) in column "E" to identify the allocation method.
My formula is:

=IF($E7="A",($D7*'Alloc'!E$7),IF($E7="B"($D7*'Alloc'!E$10).....thru "L".

However, my formula will only accept the if statments thur letter H ( 7
nested). Is there any way around this that will allow me to include the
other 7 allocation methods in my if statments?

As you can probably tell I'm pretty basic.
Thanks

Hello JayE,

Using the worksheet If function limits you to a maximum depth of seven. The answer is to use a VBA macro. Unfortunately I can not tell from your code example exactly what you are doing and how your spreadsheet information is arranged. You can e-mail me [email protected] . Let me and I can help you.

Thanks,
Leith Ross

hi,
use 2 if formulas in 2 columns, hide the first.
do the first 5 to calculate else zero
then do the last 5 picking up if the first was zero.
like this
MY data in column O ,P and Q, 1st formula in R 2nd in S
for developement and test purposes.
first 5
=IF($O2="a",$P2*$Q2,IF($O2="b",$P2*$Q3,IF
($O2="c",$P2*$Q4,IF($O2="d",$P2*$Q5,IF
($O2="e",$P2*$Q6,0)))))
the formula wrapped. suppose to be one line.
notice that is it is not a,b,c,d, or e then zero
so it would calculate if it were an a,b,c,d or e.
then hide this column
second 5
=IF($R2<>0,$R2,IF($O2="f",$P2*$Q2,IF($O2="g",$P2*$Q2,IF
($O2="h",$P2*$Q2,IF($O2="I",$P2*$Q2,$P2*$Q2)))))
notice that if the previous formula didn't produce zero ie
it calculated a number then put the contents the previous
formula but if zero then keep looking for a f,g,h,i or j
this way you don't excel the 7 cap and you have 10 if then
else.
I just tested this to make sure. you will have to change
it all for your situation. but it works.
hope it helps

>-----Original Message-----
>I am trying to create a worksheet that will allocate
operating expenses to 6
>different departments. I have 14 allocation methods on
tab "Alloc" that are
>identified with letters A thru L. On the expense
alloction tab I have
>assigend a letter(A thru L) in column "E" to identify
the allocation method.
> My formula is:
>
>=IF($E7="A",($D7*'Alloc'!E$7),IF($E7="B"($D7*'Alloc'!
E$10).....thru "L".
>
>However, my formula will only accept the if statments
thur letter H ( 7
>nested). Is there any way around this that will allow me
to include the
>other 7 allocation methods in my if statments?
>
>As you can probably tell I'm pretty basic.
>Thanks
>.
>

On Fri, 18 Feb 2005 11:41:01 -0800, "JayE" <[email protected]>
wrote:

>I am trying to create a worksheet that will allocate operating expenses to 6
>different departments. I have 14 allocation methods on tab "Alloc" that are
>identified with letters A thru L. On the expense alloction tab I have
>assigend a letter(A thru L) in column "E" to identify the allocation method.
> My formula is:
>
>=IF($E7="A",($D7*'Alloc'!E$7),IF($E7="B"($D7*'Alloc'!E$10).....thru "L".
>
>However, my formula will only accept the if statments thur letter H ( 7
>nested). Is there any way around this that will allow me to include the
>other 7 allocation methods in my if statments?
>
>As you can probably tell I'm pretty basic.
>Thanks

Take a look at VLOOKUP in HELP.

You should probably use VLOOKUP. On your Alloc worksheet, set up a table with
A-L in column 1, and your "allocation method" in column 2.

Then use a formula of the form:

=$D7 * VLOOKUP($E7, 'Alloc'!$A$1:$B$n,2)


--ron

letter number in the alphabet
a
=IF(OR(B2="a",B2="b",B2="c",B2="d",B2="e",B2="f"),IF(B2="a",1,IF(B2="b",2,IF
(B2="c",3,IF(B2="d",4,IF(B2="e",5,6))))),IF(OR(B2="g",B2="h",B2="i",B2="j",B
2="k",B2="l"),IF(B2="g",7,IF(B2="h",8,IF(B2="i",9,IF(B2="j",10,IF(B2="k",11,
12))))),IF(OR(B2="m",B2="n",B2="o",B2="p",B2="q",B2="r"),IF(B2="m",13,IF(B2=
"n",14,IF(B2="o",15,IF(B2="p",16,IF(B2="q",17,18))))),IF(OR(B2="s",B2="t",B2
="u",B2="v",B2="w"),IF(B2="s",19,IF(B2="t",20,IF(B2="u",21,IF(B2="v",22,23))
)),IF(B2="x",24,IF(B2="y",25,IF(B2="z",26,"error - type a letter")))))))
z 26
f 6
b 2
d 4
j 10
t 20
58 error - type a letter


Formula IF's-26 in one equation



"Ron Rosenfeld" <[email protected]> escribió en el mensaje
news:[email protected]...
> On Fri, 18 Feb 2005 11:41:01 -0800, "JayE"
<[email protected]>
> wrote:
>
> >I am trying to create a worksheet that will allocate operating expenses
to 6
> >different departments. I have 14 allocation methods on tab "Alloc" that
are
> >identified with letters A thru L. On the expense alloction tab I have
> >assigend a letter(A thru L) in column "E" to identify the allocation
method.
> > My formula is:
> >
> >=IF($E7="A",($D7*'Alloc'!E$7),IF($E7="B"($D7*'Alloc'!E$10).....thru "L".
> >
> >However, my formula will only accept the if statments thur letter H ( 7
> >nested). Is there any way around this that will allow me to include the
> >other 7 allocation methods in my if statments?
> >
> >As you can probably tell I'm pretty basic.
> >Thanks
>
> Take a look at VLOOKUP in HELP.
>
> You should probably use VLOOKUP. On your Alloc worksheet, set up a table
with
> A-L in column 1, and your "allocation method" in column 2.
>
> Then use a formula of the form:
>
> =$D7 * VLOOKUP($E7, 'Alloc'!$A$1:$B$n,2)
>
>
> --ron

Antonyo

Nice work. Hard to read though!!

Ron
Try:

= $E7="A" * $D7*'Alloc'!E$7 + $E7="B"*$D7*'Alloc'!E$10 +
$E7="C"*$D7*'Alloc'! and so on.

Explanation

If E7 = A, this is interpretted as 1, that part of your formula becomes
1*$D7*'Alloc'!E$7
When E7 is anything except 'A', E7=A is interpressted as 0. That part of
the formula becomes 0*$D7*'Alloc'!E$7 (ie 0)

When you group them all together, read the '+' sign as a separation between
each part of the formula and it becomes simple to read.

HTH
Steve


"Antonyo" <[email protected]> wrote in message
news:[email protected]...
> letter number in the alphabet
> a
> =IF(OR(B2="a",B2="b",B2="c",B2="d",B2="e",B2="f"),IF(B2="a",1,IF(B2="b",2,IF
> (B2="c",3,IF(B2="d",4,IF(B2="e",5,6))))),IF(OR(B2="g",B2="h",B2="i",B2="j",B
> 2="k",B2="l"),IF(B2="g",7,IF(B2="h",8,IF(B2="i",9,IF(B2="j",10,IF(B2="k",11,
> 12))))),IF(OR(B2="m",B2="n",B2="o",B2="p",B2="q",B2="r"),IF(B2="m",13,IF(B2=
> "n",14,IF(B2="o",15,IF(B2="p",16,IF(B2="q",17,18))))),IF(OR(B2="s",B2="t",B2
> ="u",B2="v",B2="w"),IF(B2="s",19,IF(B2="t",20,IF(B2="u",21,IF(B2="v",22,23))
> )),IF(B2="x",24,IF(B2="y",25,IF(B2="z",26,"error - type a letter")))))))
> z 26
> f 6
> b 2
> d 4
> j 10
> t 20
> 58 error - type a letter
>
>
> Formula IF's-26 in one equation
>
>
>
> "Ron Rosenfeld" <[email protected]> escribió en el mensaje
> news:[email protected]...
>> On Fri, 18 Feb 2005 11:41:01 -0800, "JayE"
> <[email protected]>
>> wrote:
>>
>> >I am trying to create a worksheet that will allocate operating expenses
> to 6
>> >different departments. I have 14 allocation methods on tab "Alloc" that
> are
>> >identified with letters A thru L. On the expense alloction tab I have
>> >assigend a letter(A thru L) in column "E" to identify the allocation
> method.
>> > My formula is:
>> >
>> >=IF($E7="A",($D7*'Alloc'!E$7),IF($E7="B"($D7*'Alloc'!E$10).....thru "L".
>> >
>> >However, my formula will only accept the if statments thur letter H ( 7
>> >nested). Is there any way around this that will allow me to include the
>> >other 7 allocation methods in my if statments?
>> >
>> >As you can probably tell I'm pretty basic.
>> >Thanks
>>
>> Take a look at VLOOKUP in HELP.
>>
>> You should probably use VLOOKUP. On your Alloc worksheet, set up a table
> with
>> A-L in column 1, and your "allocation method" in column 2.
>>
>> Then use a formula of the form:
>>
>> =$D7 * VLOOKUP($E7, 'Alloc'!$A$1:$B$n,2)
>>
>>
>> --ron
>
>

Yes, definitely! Use a table and VLOOKUP, PLEASE!

On Sun, 20 Feb 2005 19:59:53 +1100, "Steve" <[email protected]> wrote:

>Antonyo
>
>Nice work. Hard to read though!!
>
>Ron
>Try:
>
> = $E7="A" * $D7*'Alloc'!E$7 + $E7="B"*$D7*'Alloc'!E$10 +
>$E7="C"*$D7*'Alloc'! and so on.
>
>Explanation
>
>If E7 = A, this is interpretted as 1, that part of your formula becomes
>1*$D7*'Alloc'!E$7
>When E7 is anything except 'A', E7=A is interpressted as 0. That part of
>the formula becomes 0*$D7*'Alloc'!E$7 (ie 0)
>
>When you group them all together, read the '+' sign as a separation between
>each part of the formula and it becomes simple to read.
>
>HTH
>Steve
>
>
>"Antonyo" <[email protected]> wrote in message
>news:[email protected]...
>> letter number in the alphabet
>> a
>>
=IF(OR(B2="a",B2="b",B2="c",B2="d",B2="e",B2="f"),IF(B2="a",1,IF(B2="b",2,IF
>>
(B2="c",3,IF(B2="d",4,IF(B2="e",5,6))))),IF(OR(B2="g",B2="h",B2="i",B2="j",B
>>
2="k",B2="l"),IF(B2="g",7,IF(B2="h",8,IF(B2="i",9,IF(B2="j",10,IF(B2="k",11,
>>
12))))),IF(OR(B2="m",B2="n",B2="o",B2="p",B2="q",B2="r"),IF(B2="m",13,IF(B2=
>>
"n",14,IF(B2="o",15,IF(B2="p",16,IF(B2="q",17,18))))),IF(OR(B2="s",B2="t",B2
>>
="u",B2="v",B2="w"),IF(B2="s",19,IF(B2="t",20,IF(B2="u",21,IF(B2="v",22,23))
>> )),IF(B2="x",24,IF(B2="y",25,IF(B2="z",26,"error - type a letter")))))))
>> z 26
>> f 6
>> b 2
>> d 4
>> j 10
>> t 20
>> 58 error - type a letter
>>
>>
>> Formula IF's-26 in one equation
>>
>>
>>
>> "Ron Rosenfeld" <[email protected]> escribió en el mensaje
>> news:[email protected]...
>>> On Fri, 18 Feb 2005 11:41:01 -0800, "JayE"
>> <[email protected]>
>>> wrote:
>>>
>>> >I am trying to create a worksheet that will allocate operating expenses
>> to 6
>>> >different departments. I have 14 allocation methods on tab "Alloc" that
>> are
>>> >identified with letters A thru L. On the expense alloction tab I have
>>> >assigend a letter(A thru L) in column "E" to identify the allocation
>> method.
>>> > My formula is:
>>> >
>>> >=IF($E7="A",($D7*'Alloc'!E$7),IF($E7="B"($D7*'Alloc'!E$10).....thru "L".
>>> >
>>> >However, my formula will only accept the if statments thur letter H ( 7
>>> >nested). Is there any way around this that will allow me to include the
>>> >other 7 allocation methods in my if statments?
>>> >
>>> >As you can probably tell I'm pretty basic.
>>> >Thanks
>>>
>>> Take a look at VLOOKUP in HELP.
>>>
>>> You should probably use VLOOKUP. On your Alloc worksheet, set up a table
>> with
>>> A-L in column 1, and your "allocation method" in column 2.
>>>
>>> Then use a formula of the form:
>>>
>>> =$D7 * VLOOKUP($E7, 'Alloc'!$A$1:$B$n,2)
>>>
>>>
>>> --ron
>>
>>
>

On Sun, 20 Feb 2005 19:59:53 +1100, "Steve" <[email protected]> wrote:

>Antonyo
>
>Nice work. Hard to read though!!
>
>Ron
>Try:
>
> = $E7="A" * $D7*'Alloc'!E$7 + $E7="B"*$D7*'Alloc'!E$10 +
>$E7="C"*$D7*'Alloc'! and so on.
>
>Explanation
>
>If E7 = A, this is interpretted as 1, that part of your formula becomes
>1*$D7*'Alloc'!E$7
>When E7 is anything except 'A', E7=A is interpressted as 0. That part of
>the formula becomes 0*$D7*'Alloc'!E$7 (ie 0)
>
>When you group them all together, read the '+' sign as a separation between
>each part of the formula and it becomes simple to read.
>
>HTH
>Steve

Sure seems a lot more complicated and less flexible than a lookup table.
--ron

You forgot "harder to maintain".

"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Sun, 20 Feb 2005 19:59:53 +1100, "Steve" <[email protected]> wrote:
>
>>Antonyo
>>
>>Nice work. Hard to read though!!
>>
>>Ron
>>Try:
>>
>> = $E7="A" * $D7*'Alloc'!E$7 + $E7="B"*$D7*'Alloc'!E$10 +
>>$E7="C"*$D7*'Alloc'! and so on.
>>
>>Explanation
>>
>>If E7 = A, this is interpretted as 1, that part of your formula becomes
>>1*$D7*'Alloc'!E$7
>>When E7 is anything except 'A', E7=A is interpressted as 0. That part of
>>the formula becomes 0*$D7*'Alloc'!E$7 (ie 0)
>>
>>When you group them all together, read the '+' sign as a separation
>>between
>>each part of the formula and it becomes simple to read.
>>
>>HTH
>>Steve
>
> Sure seems a lot more complicated and less flexible than a lookup table.
> --ron