Replacing letters for figures in formula

I

Irene

I have to replace letters A, B, C which were used as a mark to figures in the
total amount formula according to A=3, B=2, C=1.
Could you advise me if I can do it in Excel?

Thank you in advance
 
B

Bernard Liengme

With the letter-grade in cell A1 use
=LOOKUP(A26,{"A","B","C","D"},{3,2,1,0}) to get numeric grade
best wishes
 
I

Irene

Excuse me, but it was not helpful.
I have in column E,F,G,I accordingly marks A, B, C, A and in column P I have
to calculate total in figures =sum (E4;H4) if A=3, B=2, C=1. and the same
for the other rows.
Please, advise
 
B

Bernard Liengme

Expanding on my first reply,
if your first row is row 4 and the letters are in E4, F4, G4 and H4 then
this formula will do the summation
=LOOKUP(E4,{"A","B","C"},{3,2,1})+LOOKUP(F4,{"A","B","C"},{3,2,1})+LOOKUP(G4,{"A","B","C"},{3,2,1})+LOOKUP(H4,{"A","B","C"},{3,2,1})
You can copy it down (or up) to the next rows and Excel will automatically
adjust the cell references.

An alternative, shorter, formula is
=SUMPRODUCT((E4:H4="A")*3+(E4:H4="B")*2+(E4:H4="C")*1)

hope this helps
 
C

Cutter

How about in P4:

=COUNTIF(E4:H4,"A")*3+COUNTIF(E4:H4,"B")*2+COUNTIF(E4:H4,"C")

Copy down as far as you need
 
B

Bernard Liengme

As my old Irish, math teacher would say
"There are more ways of killing a pig than stuffing it with butter"
 
C

Cutter

Was your old Irish math teacher a cat lover?

(Couldn't bear the thought of skinning one!)

;-
 
R

RagDyer

A little more concise:

=SUMPRODUCT((E4:H4={"A";"B";"C"})*{3;2;1})

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
R

RagDyer

Because it has the asterisk, denoting multiplication.

*ANY* mathematical operator will convert the True & False to a value - 1 or
0.

In A1 enter a
1

In B1 enter
=($A$1>0)
And copy down 4 or 5 rows.

Then in B2, add the unary:
=--($A$1>0)

And add these to the other rows
=($A$1>0)*1
=($A$1>0)+0
=($A$1>0)-0
=($A$1>0)/1

See what happens?

Now, change A1 to zero.

See what happens?
 
Top