Spredsheet problem
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 |
Spredsheet problem
|
Spredsheet problem
I see what you are sying joe but i would like to add the values as we
eg. A A B A B 40 "wildlife guy" wrote: 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 |
Spredsheet problem
PS....
I wrote: INDEX({10,15,5,...23otherValues...}, CODE(A1)-CODE("A")+1) 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 ----- "Joe User" wrote: "wildlife guy" <wildlife wrote: 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 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) |
Spredsheet problem
"wildlife guy" wrote:
I see what you are sying joe but i would like to add the values as we eg. A A B A B 40 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 ----- "wildlife guy" wrote: I see what you are sying joe but i would like to add the values as we eg. A A B A B 40 "wildlife guy" wrote: 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 |
All times are GMT +1. The time now is 09:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com