Lesson#59: If A=1, Z=26 then calculate the value of your name
This sheet can calculate the value of your name or any word by summing every letter in alphabetical order.
Here I have created a box to input a word.
I have put this formula =LEN(B2) to calculate the length of the word.
Now I have written A-Z in a column and in the next column I have written the order of the alphabet.
Now in a column I have put the formula =IF(COUNTIF($J$1:$J1,”*”)+1<=$D$12,MID($B$2,COUNTIF($J$1:$J1,”*”)+1,1),””) and I will drag it down in the column. This formula will extract the letter from the word.
In the next column, I will put the formula =IFERROR(VLOOKUP(UPPER(J2),$P$2:$Q$27,2,FALSE),0). This formula will look up the value of every letter in alphabetical order.
Now I have made the sum of values.
This is how it works.
I have provided the download link to the file. Download and try it.
Hi! I am Puspendu. I am the founder and author of Excelabcd. I am little creative person, blogger and Excel-maniac guy. I hope you enjoy my blog.
Leave a Reply