Spredsheet problem

  • Thread starter Thread starter wildlife guy
  • Start date Start date
W

wildlife guy

I need to give a numeric value to letters in individual cells. ex. A=10
B=15 c=5.
Do I need to build a chart to reference? I am in need of big help, a magic
formula.

Justin
 
wildlife guy said:
I need to give a numeric value to letters in individual cells.
ex. A=10 B=15 c=5. Do I need to build a chart to reference?
I am in need of big help, a magic formula.
Justin
Click to expand...

As usual, there are many ways to do this.

First, note that a letter already has a numeric value, e.g. CODE("A").

So, if you only need to handle uppercase characters, you could write:

INDEX({10,15,5,...23otherValues...}, CODE(A1)-CODE("A")+1)

Alternatively, you could put the 26 values in, say, X1:X26, and write:

INDEX($X$1:$X$26, CODE(A1)-CODE("A")+1)

If you need to handle lowercase as well, then write:

INDEX($X$1:$X$26, CODE(UPPERCASE(A1))-CODE("A")+1)
 
I see what you are sying joe but i would like to add the values as we

eg. A A B A B 40
 
PS....
INDEX({10,15,5,...23otherValues...}, CODE(A1)-CODE("A")+1)
Click to expand...

Of course, CODE(A1)-CODE("A")+1 could be replaced by CODE(A1)-64.

I just wanted to make the derivation of 64 clear.


----- original message -----
 
wildlife guy said:
I see what you are sying joe but i would like
to add the values as we
eg. A A B A B 40
Click to expand...

If A=10 and B=15, A+A+B+A+B is 60, not 40.

Is "A A B A B" a string in a single cell, or does A A B A B represent the
contents of 5 cells?

In either case, I am struggling with an Excel formula to calculate the sum.
I'll continue to think about it. But it appears to be a mess, in any case.

A VBA solution is concise, efficient and easy to implement. Is that
acceptable?

If A1 contains the string "A A B A B" and X1:X26 contains the 26 values for
each letter, the formula would be:

=sumcodes(A1, X1:X26)

The VBA function is:

Function sumcodes(s As String, v As Range) As Double
Dim i As Integer, c As String * 1, sc As Double
For i = 1 To Len(s)
c = UCase(Mid(s, i, 1))
If "A" <= c And c <= "Z" Then sc = sc + v.Cells(Asc(c) - 64, 1)
Next i
sumcodes = sc
End Function


----- original message -----
 
Back
Top