Excel formula help needed

dreamCatcher

dreamCatcher

Disciple
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.
 
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
 
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..!

:)
 
boogeyman said:
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
Click to expand...
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 ---
waatavr said:
can you attach some example sheet; on exactly what you are looking for...
Click to expand...
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 said:
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..!

:)
Click to expand...
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 ---
asingh said:
Is there a difference between a capital letter and a lower letter in terms of value..?
Click to expand...
No, upper case and lower case have the same values. And I will calculate only individual words, not sentences.
 
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...!
 
asingh said:
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...!
Click to expand...
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.
 
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)
Click to expand...

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...!
 
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]
pencil.png
 

Attachments

  • Count.jpg
    Count.jpg
    27.1 KB · Views: 128
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]
 

Attachments

  • Copy does not work.jpg
    Copy does not work.jpg
    23.2 KB · Views: 118
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.
 
asingh said:
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.
Click to expand...
Ahh... I see, it works now. Thanks 'asingh' :) for your time and effort.
 
^^

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.
 
Back
Top