[Solved] Assigning values to numbers-calclation method

[chandrankrishnan]

Hai
Good day to all,
Can any one help me to give a solution for assigning values to Alphabets.

Example:
if i type AB the value has to show as"3". BC means value has to be as "5".like this(A=1,B=2,C=3.........and so on)

Any method or formula can be used to achieve this.

(i tried myself by using method through Vlookup too.)

[FJCC]

Will the input always be two characters?

[chandrankrishnan]

No it will change.

[acknak]

You might try a search here; I'm pretty sure this has come up before. I believe there is a single large formula that will calculate a sum given any text.

Edit: PS:

Here is one previous discussion: viewtopic.php?f=9&t=86198

You may find more with a search for numerology or destiny

[chandrankrishnan]

Hai acknak,
Thanks for the feedback.
the formula given in previous discussions where the result is showing the count value,where i need is the sum value,what i need to change in formula.
Please advice me.
Formula:
MOD(SUMPRODUCT(MOD(CODE(UPPER(MID(SUBSTITUTE(A1;" ";"");ROW(OFFSET(INDIRECT("A1");0;0;LEN(SUBSTITUTE(A1;" ";""));1));1)))-CODE("A")+1;9)+IF(CURRENT()=0;9;0));9)+IF(CURRENT()=0;9;0)

[keme]

Hai acknak,
Thanks for the feedback.
the formula given in previous discussions where the result is showing the count value,...

No it does not. It gives the "iterated digit sum" of the raw sum you requested. To have the sum without reduction, just remove the MOD(...;9) constructs and the then unnecessary substitution of zero with 9.
=SUMPRODUCT(CODE(UPPER(MID(SUBSTITUTE(A1;" ";"");ROW(OFFSET(INDIRECT("A1");0;0;LEN(SUBSTITUTE(A1;" ";""));1));1)))-CODE("A")+1)

[chandrankrishnan]

Wow....thanks alot keme / acknak.

Its working fine.

[acknak]

Sorry, I thought the sum was what the previous poster was after, and I didn't check the formula carefully.