Score conversion trailing zero issue

[tigerzen]

Got a simple formula to convert goals and behinds into points, each goal is worth 6 points and a behind is worth 1 point. Formula works in all cases except when you have something like 17.10, this should calculate to 17 x 6 + 10 x 1 which equals 112 points. As you can see in the file, it drops the 0 from 17.10 and calculates 103. I've tried Custom formats but no joy here. Is there a way to force TEXTAFTER to recognise the 0 or is there another quick solution?

Book3
	A	B	C	D	E	F	G	H	I	J	K
1	Scores					Points
2	3.2	4.5	6.7	9.8		20	29	43	62
3	7.1	9.5	13.8	17.10		43	59	86	103		1
Sheet1

Cell Formulas
Range	Formula
F2:I3	F2	=INT(A2)*6+(TEXTAFTER(A2,"."))
K3	K3	=TEXTAFTER(D3,".")

 

[I_know_nuffin]

(LEFT(A2,FIND(".",A2)-1)*6) + MID(A2,FIND(".",A2)+1,5)

MID(A2,FIND(".",A2)+1,5)

 

[Alex Blakenburg]

Your issue is that you are wanting to do string manipulation but entering the data as if it were a number.
You need to force your entered data to be interpreted as text.
Option 1) How do you feel about formatting column A-D as text "before" entering your data (or doing a paste values) ?
If you do that your figures will be held as Text and your formulas should work.

Option 2) Use a symbol that Excel won't convert to a number or a date (ie not a period or "/" or "-")
You will need to change the Int part of the formula as well using this method.
If you have a numeric keypad for entry then the plus sign is the most convenient ie 17+10

 

[Fluff]

Another option would be to enter single scores as .01 like

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Scores					Points
2	3.02	4.05	6.07	9.08		20	29	43	62
3	7.01	9.05	13.08	17.1		43	59	86	112
Master

Cell Formulas
Range	Formula
F2:I3	F2	=INT(A2)*6+MOD(A2,1)*100

 

[tigerzen]

Just to provide some context data comes to me in a single cell ie 3.2 4.5 6.7 9.8, I then use Text to Columns to input each score within its own cell.

 

[Fluff]

So why not just use one of the suggestions from this thread of yours. Score conversion

 

[Alex Blakenburg]

Would this work for you ?

Book1
	A	B	C	D	E	F	G	H	I	J
1	Source (Hide Column)	Scores					Points
2	3.2 4.5 6.7 9.8	3.2	4.5	6.7	9.8		20	29	43	62
3	7.1 9.5 13.8 17.10	7.1	9.5	13.8	17.10		43	59	86	112
Sheet2

Cell Formulas
Range	Formula
G2:J3	G2	=INT(B2)*6+(TEXTAFTER(B2,"."))
B2:E3	B2	=TEXTSPLIT(A2," ")
Dynamic array formulas.

 

[tigerzen]

Would this work for you ?

Book1
	A	B	C	D	E	F	G	H	I	J
1	Source (Hide Column)	Scores					Points
2	3.2 4.5 6.7 9.8	3.2	4.5	6.7	9.8		20	29	43	62
3	7.1 9.5 13.8 17.10	7.1	9.5	13.8	17.10		43	59	86	112
Sheet2

Cell Formulas
Range	Formula
G2:J3	G2	=INT(B2)*6+(TEXTAFTER(B2,"."))
B2:E3	B2	=TEXTSPLIT(A2," ")
Dynamic array formulas.

Yes, this works well.

 

[tigerzen]

So why not just use one of the suggestions from this thread of yours. Score conversion

Yes, also a possibility, but I was curious as to what was preventing my formula from working.

 

[Peter_SSs]

Just to provide some context data comes to me in a single cell ie 3.2 4.5 6.7 9.8, I then use Text to Columns to input each score within its own cell.

When you are doing Text to Columns, when you get to Step 3, click in the first column down the bottom and select Text up the top like in the image below. Repeat for each of the other columns before clicking Finish. Then your formulas in F2:I3 in post #1 will return the correct results.