| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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, |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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, |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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, |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
| Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
| Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
| formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
| How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Linear Mode
