Excel formula help needed

[dreamCatcher]

I am very much a novice with MS excel, and need guidance with the formula for the following.

First of all I would like to define to the excel sheet that A=5 B=1 C=8 D=3 E=4.....L=5.....N=7.....V=3.....X=2 Y=8 Z=4 and so on for the rest of the alphabets. Mind you, this is not A=1 B=2 C=3, The value assigned to each alphabets is custom and as defined by me, and in no particular order.

Next I need to sum the values of each alphabet in a word that I type in the excel grid.

Ex. If I type ENCLAVE in one of the grids, it should show =36 in the adjacent grid to the right. (i.e. 4+7+8+5+5+3+4 referring to the defined values). A simple summing math operation.

Likewise if I copy paste n number of words into the excel one below the other like a list, it should give me the =ANS next to each of them without me asking it to calculate.

So, how do I go about doing this? All help is appreciated.

 

[asingh]

Not possible using standard excel functions. Either you use VBA or write a customized UDF.

 

[boogeyman]

You can use a quick and dirty Find-Replace to replace "A" with "5+", "B" with "1+" and so on...

But as asingh said, not possible with standard functions

 

[waatavr]

can you attach some example sheet; on exactly what you are looking for...

 

[asingh]

Well..I guess it can be done....!

=SUM(LEN(C9)-LEN(SUBSTITUTE(C9,"S","")))

This will count the number of instances of a character. In this formula it is "S". Then it can be multiplied easily to the look up integer equivalent for "S". Probably 26 helper columns and summing the total in the end shall give the aggregate..!

 

[dreamCatcher]

You can use a quick and dirty Find-Replace to replace "A" with "5+", "B" with "1+" and so on...

But as asingh said, not possible with standard functions

Thanks for the idea, but I don't know how to start with it. With this method it looks doable. If A,B.... replaced with 5+,1+.... as you have mentioned will it sum at the end?

--- Updated Post - Automerged ---

can you attach some example sheet; on exactly what you are looking for...

Its very simple, I just type a word (any word) and it should refer to the value assigned to each alphabet and give the result by adding all the letter values of the word.

 

[asingh]

Is there a difference between a capital letter and a lower letter in terms of value..?

 

[dreamCatcher]

Well..I guess it can be done....!

=SUM(LEN(C9)-LEN(SUBSTITUTE(C9,"S","")))

This will count the number of instances of a character. In this formula it is "S". Then it can be multiplied easily to the look up integer equivalent for "S". Probably 26 helper columns and summing the total in the end shall give the aggregate..!

Never thought its going to be soo complicated, but why should you count the number of instances of a character? Can it not just refer to the defined values, taking each alphabet as an independent defined character irrespective of how many times an alphabet appears.

--- Updated Post - Automerged ---

Is there a difference between a capital letter and a lower letter in terms of value..?

No, upper case and lower case have the same values. And I will calculate only individual words, not sentences.

 

[asingh]

Does it not work like this:

A = 1

B = 10

C = 8

So ABCC = 1 + 10 + 8 + 8

= 27

You will have to count how many A,B,C are in the full text right. Then sum up against an equivalent value.

Tell me about the capitals and lower case I asked. Will send you a winner...!

 

[dreamCatcher]

Does it not work like this:

A = 1
B = 10
C = 8

So ABCC = 1 + 10 + 8 + 8
= 27
You will have to count how many A,B,C are in the full text right. Then sum up against an equivalent value.

Tell me about the capitals and lower case I asked. Will send you a winner...!

Perfecto mate, that's it :clap: . Counting how many A,B,C are in the full text is not required. Just the sum up against an equivalent value is required. And this should work with a list of words pasted at once, giving me individual sums of each word next to it. Nothing more.

 

[asingh]

Ok..

Well here you go.....!

Calculate_Letters.xls - 4shared.com - online file sharing and storage - download

The main formula which I used was:

=IF(SUM(LEN($A6)-LEN(SUBSTITUTE(UPPER($A6),UPPER(F$5),"")))<>0,VLOOKUP(UPPER(F$5),Sheet2!$B$4:$C$55,2,0)*SUM(LEN($A6)-LEN(SUBSTITUTE(UPPER($A6),UPPER(F$5),""))),0)

It will basically:

1. Get the count of a letter.

2. Look for the converted upper case letter against a table.

3. Pull out the value corresponding for (2).

4. Multiply (3) with (1).

5. Do this for all 26 letters.

6. Sum them up in the end.

Sheet 2 has the table and a value against each letter. You can update that. Just ignore the lower case letters. They are not used.

Hope this helps...!

 

[dreamCatcher]

Oh my god, you are the man :thanks!::goodjob: :food4:. It works perfectly even for lower case words. Now can I rearrange the columns like in the picture below and probably push the individual A, B, C....columns to the right, as I don't need them. And I will do this for hundreds of words but the sheet accepts only till line 41, how do I increase? Thanks again.

[attachment=8413:14848.attach]

 

[asingh]

Errrm...

Just select row 41. Copy it.

And paste it down as much as you want.

 

[waatavr]

^^ good solution

 

[dreamCatcher]

Don't worry about my previously attached image, I can do the multi column listing on a separate sheet. Now this is the last question, why cant I copy the sum values to another sheet? When I paste them, I just get zeros instead of the sum values. Any idea why?

[attachment=8414:14849.attach]

 

[asingh]

When you directly copy paste in MS-Excel, it picks up the formulas. The cells actually do not contain the SUM values. They have formulas. What you "see" is the resultant.

To negate this. Do a copy (control+C), but your cursor on the destination cell. Then do a right click, paste special. Choose VALUES.

 

[dreamCatcher]

When you directly copy paste in MS-Excel, it picks up the formulas. The cells actually do not contain the SUM values. They have formulas. What you "see" is the resultant.

To negate this. Do a copy (control+C), but your cursor on the destination cell. Then do a right click, paste special. Choose VALUES.

Ahh... I see, it works now. Thanks 'asingh' for your time and effort.

 

[agantuk]

Ok..
Well here you go.....!

Bingo! VLOOKUP is outstanding and powerful but lesser known for the kind of utility it provides

 

[asingh]

^^

There are a ton of more functions much more powerful. Just one needs to tap into Excel.

Probably the diamond amongst them all is SUMPRODUCT.

 

[agantuk]

^ Just read the description and saw a few examples of SUMPRODUCT. Very interesting