help with inserting a cell value into a named formula

[Guest]

If someone could help me solve this conundrum, I'd be wonderfully grateful...

The Setup - I've got 2 columns, one named "text", one named "numbers"; lets
say A,B,C,& D in A1-A4, and 1,2,3,&4 in B1-B4. I also have four formulas -
let's say they're named AA,BB,CC,&DD. (AA would multiply by 2, BB multiply by
3, CC by 4, DD by 5).

The Problem - In column C, I want to have an IF function that...
logical_test = looks at whether the value of a cell in a "text" column cell
is A,B,C,orD,
value_if_true = uses the corresponding formula (AA,BB,CC,orDD) inserting the
number value in the "numbers" column cell in the formula.

the IF function is set up as... =IF(A1="A",AA,IF(A1="B",BB,IF(A1="C",CC,DD)))

I can't figure out how to set up the named formulas so that they include the
value of the respective cell in the "numbers" column. I thought that using
the following formulas would work...
AA = (numbers*2)
BB = (numbers*3)
CC = (numbers*4)
DD = (numbers*5)
but they don't.

So if anyone could help me or give some tips about how I could make this
happen, I'd really appreciate it. Thanks!

Ed

 

[pinmaster]

Hi,
I'm not an expert but how about something like this:

=IF(AND(CODE(UPPER(A2))>=65,CODE(UPPER(A2))<=69),B2*(CODE(UPPER(A2))-63),"")

this formula will use the CODE function to determine if A2 contains an
A, B, C or D...if it finds one of those letters then it will multiply
B2 by CODE value of the letter minus 63.

HTH
JG