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*'Allo c'!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

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*'All oc'!
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"
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*'All oc'!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",B 2="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(B 2="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" escribió en el mensaje
...
On Fri, 18 Feb 2005 11:41:01 -0800, "JayE"

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*'All oc'!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" wrote in message
...
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",B 2="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(B 2="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" escribió en el mensaje
...
On Fri, 18 Feb 2005 11:41:01 -0800, "JayE"

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*'All oc'!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" 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" wrote in message
...
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",B 2="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(B 2="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" escribió en el mensaje
...
On Fri, 18 Feb 2005 11:41:01 -0800, "JayE"

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*'All oc'!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" 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" wrote in message
...
On Sun, 20 Feb 2005 19:59:53 +1100, "Steve" 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