Replacing letters for figures in formula

[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

[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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Irene" <[email protected]> wrote in message
news:[email protected]...
>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

[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


"Irene" wrote:

> 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

[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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Irene" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "Irene" wrote:
>
>> 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

[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

[Bernard Liengme]

As my old Irish, math teacher would say
"There are more ways of killing a pig than stuffing it with butter"
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Cutter" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Cutter
> ------------------------------------------------------------------------
> Cutter's Profile:
> http://www.excelforum.com/member.php...fo&userid=9848
> View this thread: http://www.excelforum.com/showthread...hreadid=498405
>

[Cutter]

Was your old Irish math teacher a cat lover?

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

;-)

[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!
==============================================

"Cutter" <[email protected]> wrote in
message news:[email protected]...
>
> Was your old Irish math teacher a cat lover?
>
> (Couldn't bear the thought of skinning one!)
>
> ;-)
>
>
> --
> Cutter
> ------------------------------------------------------------------------
> Cutter's Profile:
http://www.excelforum.com/member.php...fo&userid=9848
> View this thread: http://www.excelforum.com/showthread...hreadid=498405
>

[Cutter]

RagDyer

Very nice. But why does it work without the double unary?

[Irene]

Thank you guys!
Bernard, I used your way as a first reply.
Thanks a lot!!!!

"Irene" wrote:

> 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

[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?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Cutter" <[email protected]> wrote in
message news:[email protected]...
>
> RagDyer
>
> Very nice. But why does it work without the double unary?
>
>
> --
> Cutter
> ------------------------------------------------------------------------
> Cutter's Profile:
http://www.excelforum.com/member.php...fo&userid=9848
> View this thread: http://www.excelforum.com/showthread...hreadid=498405
>

[Cutter]

Thanks RD.