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