Gambling Decimals to Fractions - Surely simple in Excel? Anyone able to help?

[Orange777]

Hi,

I am a Newbie so please let me know if I break any rules! I have spent an hour looking at how to convert decimals into horse racing fraction . On my Excel 2013 I can use the function button but they are mathematical fractions.

Ideally I need the maths that sits behind this:

Betting Zone | Odds Converter, Fraction Converter, Decimal Converter

I found this formula that does it the other way:

=(VALUE(LEFT(A1,SEARCH("/",A1)-1))/VALUE(MID(A1,SEARCH("/",A1)+1,LEN(A1)-SEARCH("/",A1))))

However the results were wrong so I added a 1 in to give:

=1+(VALUE(LEFT(A1,SEARCH("/",A1)-1))/VALUE(MID(A1,SEARCH("/",A1)+1,LEN(A1)-SEARCH("/",A1))))

This now works for me.

Any help would be great. Seems to be the holy grail of questions. I have looked all over!

I am not great with VBA - can load it, but in practical terms I need to be able to type in cell F3 (for example) 3.25 and it to tell convert into next cell that it is equal to 9/4

THANK YOU!!!!!

 

[Orange777]

Thank you guys!!! Amazing help!!!!

 

[TheRinger]

This is how I do it. You would add +1 to the end of the formula for your format style.

IF(AND(LEN(C11)=3,MID(C11,2,1)="/"),MID(C11,1,1)/MID(C11,3,1),C11)

<colgroup><col width="90"></colgroup><tbody>
</tbody>

<colgroup><col width="90"></colgroup><tbody>
</tbody>

-- removed inline image ---

 

[shg]

FormR's suggestion:

Row\Col	A​	B​	C​	D​
1​	2​	1/1​	B1: =A1-1	Format of B1: ??/??
2​	2.25​	5/4​
3​	2.5​	3/2​
4​	2.75​	7/4​
5​	3​	2/1​
6​	3.25​	9/4​
7​	3.5​	5/2​
8​	3.75​	11/4​
9​	4​	3/1​
10​	4.25​	13/4​
11​	4.5​	7/2​
12​	4.75​	15/4​
13​	5​	4/1​

 

[FormR]

@Shg - I bet you had dollar(fr)'s in your eye when you spotted the title of this thread

 

[Orange777]

This is how I do it. You would add +1 to the end of the formula for your format style.

IF(AND(LEN(C11)=3,MID(C11,2,1)="/"),MID(C11,1,1)/MID(C11,3,1),C11)

<tbody>
</tbody>

<tbody>
</tbody>

-- removed inline image ---

Thank you TheRinger! Another option to try! Appreciated!

 

[Orange777]

Thank you - shg - I went with FormR's suggestion seems ok so far!

 

[TheRinger]

After rereading what you wanted I think this is what your looking for:

=(LEFT(A1,(FIND("/",A1,1)-1)))/(MID(A1,FIND("/",A1)+1,256))+1

 

[Orange777]

After rereading what you wanted I think this is what your looking for:

=(LEFT(A1,(FIND("/",A1,1)-1)))/(MID(A1,FIND("/",A1)+1,256))+1

Thank you TheRinger, I need to fathom out why the first one may not work. Really appreciated!

 

[TheRinger]

This is the only way I know to do the decimal odds to fractional odds conversion. Of course you would hide the last 3 columns.

horse#	Input of decimal	Vlookup formula	Decimal	Fractional	Probability %
1	3.25	9/4	3	2/1	33.33
2	3.3	23/10	3.1	21/10	32.26
3		SCR	3.2	11/5	31.25
4	3.2	11/5	3.25	9/4	30.77
5	3	2/1	3.3	23/10	30.3
		=IF(B2,VLOOKUP(B2,$D$6:$E$6,2,TRUE),"SCR")

<tbody>
</tbody>

 

[focuswiz]

I really like the solution in this thread but since it does not give me exactly what I am looking for, I will post the alternative I shared elsewhere.

This tries various denominators from 1 to 10 and picks one that seemingly produces something close to a whole number.

=SMALL(IF(ROUND((F3-1)*ROW(A$1:A$10),0)*(ABS(ROUND((F3-1)*ROW(A$1:A$10),3)-ROUND((F3-1)*ROW(A$1:A$10),0))=0)=0,"",ROUND((F3-1)*ROW(A$1:A$10),0)*(ABS(ROUND((F3-1)*ROW(A$1:A$10),3)-ROUND((F3-1)*ROW(A$1:A$10),0))=0)),1)&"/"&MATCH(TRUE,ABS(ROUND((F3-1)*ROW(A$1:A$10),3)-ROUND((F3-1)*ROW(A$1:A$10),0))=0,0)

I could have used the MIN function instead of SMALL, but was playing with a count of zeros (counting them to take the smallest non zero number) but converting the zeros to spaces made things easier.