convert letter to number in a column

[michael coughlin]

I want to convert the letter "W" to "5" and the letter "L" to "-5" in a column

 

[jtakw]

Hi,

Just curious, how did the "W" and "L" get in Column F in the first place, if it's manually entered, why not enter 5 and -5 instead, if it's by formula, then change your formula's output accordingly (instead of W, 5, instead of L, -5), or are you wanting to ADD 5 for W, SUBSTRACT 5 for L, in column G?

Haven't played Hearts in many years, forgot how the scoring works, but may be this?

Excel 2010
	A	B	C	D	E	F	G
1	MIKE	NORTH	EAST	WEST	SHOOT MOON	W/L	WON/LOST
2	28	27	78	102		-5
3	44	57	112	104		5
4	78	52	37	84		-5
5	46	110	73	83	10	5
6	60	66	109	77	15	5
7	71	63	104	22		-5
Sheet25

Cell Formulas
Range	Formula
F2	=IF(MIN(A2:D2)=A2,5,-5)

Assuming you're Mike, didn't you actually Lose the first game? (You had W in column F for the first game)

 

[Rick Rothstein]

you're making this too difficult. I want to put a W in column F to equal $5.00 or an L to equal -$5.00

Where is the $5 or -$5 located at... in the "Shoot Moon" column? If so, what about the other numbers in that column other than $5 and -$5... what should be done with them?

 

[markmzz]

I want to convert the letter "W" to "5" and the letter "L" to "-5" in a column

Hi!

Try this in G4 and copy down

=SUMPRODUCT(B4:D4-A4)+E4+IF(F4="W",5,IF(F4="L",-5,0))

Or

Array Formula - Use Ctrl+Shift+Enter to enter the formula

=SUM(B4:D4-A4,E4,(F4={"W";"L"})*{5;-5})


Markmzz

 

[rlv01]

As markmzz suggests, why bother to replace W/L with 5/-5 in column F? Instead, change the formula in column G. Another example:

=IF(F4="W",SUM(B4-A4,C4-A4,D4-A4,E4) +5,IF(F4="L",SUM(B4-A4,C4-A4,D4-A4,E4) -5,SUM(B4-A4,C4-A4,D4-A4,E4:F4)))

 

[michael coughlin]

columns a to d are the score for each player. each point equals $1. the person with the lowest score wins. if you shoot the moon you win $5 from each player. if you win the game you win $5 from each player. column g shows my won/loss dollars for each hand. I just need a formula for column f for $ amount won as well as wins and losses

 

[michael coughlin]

last sentence should read. Need a formuls for column F for $ amount won or lost as well as wins and losses,

 

[markmzz]

columns a to d are the score for each player. each point equals $1. the person with the lowest score wins. if you shoot the moon you win $5 from each player. if you win the game you win $5 from each player. column g shows my won/loss dollars for each hand. I just need a formula for column f for $ amount won as well as wins and losses

last sentence should read. Need a formuls for column F for $ amount won or lost as well as wins and losses,

Hi!

If I understand what you want now, then the formula below can helps:

In F4 and copy down

=LOOKUP(SUMPRODUCT(B4:D4-A4),{-9999;0;1},{-5;0;5})

Markmzz

 

[Rick Rothstein]

last sentence should read. Need a formuls for column F for $ amount won or lost as well as wins and losses,

So now you want two values in one cell? Instead of trying to describe what you want in words, why don't you simply manually fill out Column F on sample worksheet you posted with the value(s) you want in those cells so that we know exactly what you are after without having to guess at what you are trying to describe?

 

[michael coughlin]

https://www.dropbox.com/s/wumxr1et6zj4ssn/HEARTS.xlsx?dl=0

 

[markmzz]

https://www.dropbox.com/s/wumxr1et6zj4ssn/HEARTS.xlsx?dl=0

Maybe this in F4 and copy down:

[COLOR=#0000ff]=SUMPRODUCT((B4:D4-A4>0)*5-(B4:D4-A4<0)*5)[/COLOR]

By the way, I think that the value in F4 must to be 5 and not 15.

Markmzz