Text to Number dart scoreboard

[brian7mcdee]

Hi

I've had a good search of the forum regarding this problem. There are a few threads about dart scoreboards but none that match what I'm thinking of.

Basically, I've been trying to figure out a way that I can input in three columns what each dart scores in text so it returns the total value of the three darts which is then subtracted from the cell above in the fourth:

EG: (T=Treble D=Double S=Single)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Dart 1[/TD]
[TD]Dart 2[/TD]
[TD]Dart 3[/TD]
[TD]501[/TD]
[/TR]
[TR]
[TD]T20[/TD]
[TD]D5[/TD]
[TD]S1[/TD]
[TD]430[/TD]
[/TR]
[TR]
[TD]S20[/TD]
[TD]S5[/TD]
[TD]S1[/TD]
[TD]404[/TD]
[/TR]
</tbody>[/TABLE]

A friend suggested VLOOKUP but that has confused me more. Any help/pointers would be gratefully appreciated. Thanks.

 

[Vidar]

Hi
In cell D2 try this formula:
=D1-(IF(LEFT(A2,1)="T",3*MID(A2,2,LEN(A2)-1),IF(LEFT(A2,1)="D",2*MID(A2,2,LEN(A2)-1),MID(A2,2,LEN(A2)-1)+0))+IF(LEFT(B2,1)="T",3*MID(B2,2,LEN(B2)-1),IF(LEFT(B2,1)="D",2*MID(B2,2,LEN(B2)-1),MID(B2,2,LEN(B2)-1)+0))+IF(LEFT(C2,1)="T",3*MID(C2,2,LEN(C2)-1),IF(LEFT(C2,1)="D",2*MID(C2,2,LEN(C2)-1),MID(C2,2,LEN(C2)-1)+0)))

Vidar

 

[mikerickson]

You can do this with a name
Name: mySum
RefersTo: =EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$A1&"+"&Sheet1!$B1&"+"&Sheet1!$C1,"T","3*"),"D","2*"), "S", "1*"))

Since this uses relative addressing, a cell in row 1 should be active when entering the name definition.
Then putting =mySum in a cell will return the sum of columns A,B,C of that row.

 

[brian7mcdee]

Fantastic guys, thanks very much.