1

In Google Sheets, I have a system of scoring that uses letters paired with numerical values.

letters values
A 26
B 25
C 24
D 23
E 22
F 21

In my data, I have columns that are populated with letters and for the letters in each row I want to SUM their associated values.

Cat.1 Cat.2 Cat.3 Cat.4 SUM
A F F B 93
B E A A 99
C D D E 92

In the table above the first SUM returns 93 because SUM(A,F,F,B) is equivalent to SUM(26,21,21,25)=93

I have tried to find an answer online, but I'm having a hard time finding one that is specific to my problem.

Please note that I have very basic knowledge of Google Sheets/Excel, so I would like to keep it as simple as possible.

0

1 Answer 1

0

Formula

=SUM(
   ARRAYFORMULA(
     91-CODE(range_of_letters)))

Explanation

This formula leverages the CODE function to convert the capital letters to their associated values.

  1. The CODE function returns a value for each capital letter:
    CODE("A")= 65
    CODE("B")= 66
    CODE("C")= 67   // and so on 
    
  2. That value is then subtracted from 65 (the value for A) so that the each letter is now equivalent to
    65-CODE("A")=  0
    65-CODE("B")= -1
    65-CODE("C")= -2  // and so on 
    
  3. Then 26 is added to each value so the letters are now equivalent to
    91-CODE("A")= 26
    91-CODE("B")= 25
    91-CODE("C")= 24  // and so on 
    
  4. The calculation is placed in an ARRAYFORMULA function so that a range of letters can be converted at once, and the resulting array is passed to a SUM function:
    =SUM(ARRAYFORMULA(91-CODE("A","B","C"))) 
    =SUM({91-65, 91-66, 91-67})
    =SUM({26, 25, 24})
    =75
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.