Replacing letters for figures in formula
 

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

Replacing letters for figures in formula
 

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" wrote in message
...
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

Replacing letters for figures in formula
 

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

Replacing letters for figures in formula
 

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" wrote in message
...
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

Replacing letters for figures in formula
 

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

Replacing letters for figures in formula
 

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" wrote in
message ...

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

Replacing letters for figures in formula
 

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

Replacing letters for figures in formula
 

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" wrote in
message ...

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

Replacing letters for figures in formula
 

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

Replacing letters for figures in formula
 

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

Replacing letters for figures in formula
 

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$10)
And copy down 4 or 5 rows.

Then in B2, add the unary:
=--($A$10)

And add these to the other rows
=($A$10)*1
=($A$10)+0
=($A$10)-0
=($A$10)/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" wrote in
message ...

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

Replacing letters for figures in formula
 

Thanks RD.


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=498405