using if in a range
 

I am trying to workout numerology in excel:

I have number 1-9 in row - 1
Alphabets A-I in row - 2
Alphabets J-R in row - 3
Alphabets S-Z in row - 4

I wish to know if I input a value from "A-Z" in different cells, I should
get corresponding numeric values say A20=S,B20=A,C20=I in three different
cells should return A21=1,B21=1,C21=9

Can somebody help me resolve this?

Thanks,

using if in a range
 

=MOD(CODE(A20)-65,9)+1

Or, if the numbers in row 1 might change:

=INDEX($A$1:$A$20,MOD(CODE(A$20)-65,9)+1)




"Sai" wrote in message
...
I am trying to workout numerology in excel:

I have number 1-9 in row - 1
Alphabets A-I in row - 2
Alphabets J-R in row - 3
Alphabets S-Z in row - 4

I wish to know if I input a value from "A-Z" in different cells, I should
get corresponding numeric values say A20=S,B20=A,C20=I in three different
cells should return A21=1,B21=1,C21=9

Can somebody help me resolve this?

Thanks,

using if in a range
 

Or, if the positions of numbers and letters could change, this covers it:

=INDEX($A$1:$I$1,MAX(INDEX(($A$2:$I$4=A20)*COLUMN( $A$2:$I$4),)))



"Steve Dunn" wrote in message
...
=MOD(CODE(A20)-65,9)+1

Or, if the numbers in row 1 might change:

=INDEX($A$1:$A$20,MOD(CODE(A$20)-65,9)+1)




"Sai" wrote in message
...
I am trying to workout numerology in excel:

I have number 1-9 in row - 1
Alphabets A-I in row - 2
Alphabets J-R in row - 3
Alphabets S-Z in row - 4

I wish to know if I input a value from "A-Z" in different cells, I should
get corresponding numeric values say A20=S,B20=A,C20=I in three different
cells should return A21=1,B21=1,C21=9

Can somebody help me resolve this?

Thanks,

using if in a range
 

=MOD(CODE(A20)-65,9)+1

Your formula fail if A20 is a lower case letter

Try this one:
=MOD(CODE(UPPER(A20))-65,9)+1



"Steve Dunn" wrote:

=MOD(CODE(A20)-65,9)+1

Or, if the numbers in row 1 might change:

=INDEX($A$1:$A$20,MOD(CODE(A$20)-65,9)+1)




"Sai" wrote in message
...
I am trying to workout numerology in excel:

I have number 1-9 in row - 1
Alphabets A-I in row - 2
Alphabets J-R in row - 3
Alphabets S-Z in row - 4

I wish to know if I input a value from "A-Z" in different cells, I should
get corresponding numeric values say A20=S,B20=A,C20=I in three different
cells should return A21=1,B21=1,C21=9

Can somebody help me resolve this?

Thanks,

using if in a range
 

In A21: =SUMPRODUCT(($A$2:$I$4=A20)*$A$1:$I$1)

copy across to C21



"Sai" wrote:

I am trying to workout numerology in excel:

I have number 1-9 in row - 1
Alphabets A-I in row - 2
Alphabets J-R in row - 3
Alphabets S-Z in row - 4

I wish to know if I input a value from "A-Z" in different cells, I should
get corresponding numeric values say A20=S,B20=A,C20=I in three different
cells should return A21=1,B21=1,C21=9

Can somebody help me resolve this?

Thanks,