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

 

[michael coughlin]

there is no code in your answer

 

[michael coughlin]

If I win the game I get $5 from each player. If I lose the game I pay $5 to the winner

 

[markmzz]

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

there is no code in your answer

If I win the game I get $5 from each player. If I lose the game I pay $5 to the winner

Hi!

There is no code, just a formula. Look better the formula above.

Here is my formula (not code) with your data in the range H4:H122:

	A	B	C	D	E	F	G	H	I
1	HEARTS TOTALS
2
3	MIKE	NORTH	EAST	WEST	SHOOT MOON	W/L	WON/LOSS $	Formula
4	28	27	78	102		15	138	5	=SUMPRODUCT((B4:D4-A4>0)*5-(B4:D4-A4<0)*5)
5	44	57	112	104		15	156	15
6	78	52	37	84		-5	-66	-5
7	46	110	73	83	10	15	153	15
8	60	66	109	77	15	15	102	15
9	71	63	104	22		-5	-29	-5
10	52	65	91	102	15	15	132	15
11	35	68	31	100		-5	89	5
12	59	48	26	101		-5	-7	-5
13	43	82	70	117	10	15	165	15
14	91	93	59	117	-5	-5	-14	5
15	55	95	86	103		15	134	15
16	78	83	102	24	-5	-5	-35	5
17	6	92	100	36		15	225	15
18	30	107	34	37	15	15	118	15
19	91	85	102	86		-5	-5	-5
20	80	114	75	95		-5	39	5
21	1	64	101	42	15	15	234	15
31	64	101	81	92		15	97	15
32	96	82	54	106		-5	-51	-5
33	44	72	86	110		15	151	15
34	61	58	38	103			16	-5
35	65	87	95	117			104	15
121	39	123	125	103	30		264	15
122	16	109	112	101	30		304	15
123
****	*******	*****	******	******	******	*****	*************	**********	*******************************************

<tbody>
</tbody>

Markmzz

 

[michael coughlin]

some are correct and some are nor

 

[markmzz]

some are correct and some are nor

Which and why?

Markmzz

 

[markmzz]

If I win the game I get $5 from each player. If I lose the game I pay $5 to the winner

some are correct and some are nor

Which and why?

Markmzz

Hi!

I think that now I see what you want.

So, enter the formula below in H4 and copy down.

=IF(AND(INDEX(A4 < B4:D4,)),COLUMNS(B:D),-1)*5
​

After that, compare the column H with the wanted values of column E.

Only the value in F4 must to -5 and not 15 (because 28 > 27).

	A	B	C	D	E	F	G	H	I
3	MIKE	NORTH	EAST	WEST	SHOOT MOON	W/L	WON/LOSS $	Formula
4	28	27	78	102		15	138	-5	=IF(AND(INDEX(A4 < B4:D4,)),COLUMNS(B:D),-1)*5<b4:d4,)),columns(b:d),-1)*5< font=""></b4:d4,)),columns(b:d),-1)*5<>
5	44	57	112	104		15	156	15
6	78	52	37	84		-5	-66	-5
7	46	110	73	83	10	15	153	15
8	60	66	109	77	15	15	102	15
9	71	63	104	22		-5	-29	-5
10	52	65	91	102	15	15	132	15
11	35	68	31	100		-5	89	-5
12	59	48	26	101		-5	-7	-5
13	43	82	70	117	10	15	165	15
14	91	93	59	117	-5	-5	-14	-5
15	55	95	86	103		15	134	15
16	78	83	102	24	-5	-5	-35	-5
17	6	92	100	36		15	225	15
18	30	107	34	37	15	15	118	15
19	91	85	102	86		-5	-5	-5
20	80	114	75	95		-5	39	-5
21	1	64	101	42	15	15	234	15
31	64	101	81	92		15	97	15
32	96	82	54	106		-5	-51	-5
33	44	72	86	110		15	151	15
123
****	*****	*****	******	******	******	*****	*************	**********	*******************************************

<tbody>
</tbody>

Markmzz

 

[michael coughlin]

That does it. Thank you very much

 

[markmzz]

That does it. Thank you very much

You are welcome and thanks for the return.

By the way, what is the rules for the column Shoot Moon?

Markmzz