Topic: Converting Fractions Into Decimal In Excel

[GillE]

The Amazing Facts entry for today has got me thinking.  If the odds of an event happening are expressed as a fraction in one Excel cell which is formatted to show text, how could I convert them into decimal in another cell?  For example, if cell C3 gives the odds as being "2/7", how could I make cell C4 convert C3 into "0.286"?

Gill

[Sandra]

Cant help you with that one sorry Gill, as I dont use excel  

[GillE]

Hi Sandra

Would it be possible with a different spreadsheet?

Gill

[Sandra]

I dont do spreadsheets either sorry Gill.

Lonas hubby is a wizard at excel apperently, so if she sees this tomorrow I am sure she will ask him how to do it for you  

[sam]

all you have to do is type

Code: [Select]

=sum(2/7)

into an excel cell, that simple.

In fact on a newish version of it you dont even have to do this, literally typing 2/7 will do it.

This is, however, assuming that you have the cell properties right. To check this right click on the particular cell. Then click "Format Cells" and choose the option you want. If you use general or number you will get your decimal value.

[GillE]

Hi Sam

Yes, that works if you know that the fraction happens to be 2/7.  However, I'm looking to find a way to automate the process for columns of many different fractions without having to type in each number manually.

Fortunately, I've been able to knock up a macro that will do what I want for 3 columns of fractions up to 100 cells long:

Range("C3:C100").Select
    ActiveWindow.ScrollRow = 1
    Selection.TextToColumns Destination:=Range("H3"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1))
    Range("C3:D100").Select
    ActiveWindow.ScrollRow = 1
    ActiveWindow.SmallScroll Down:=75
    Range("D3:D100").Select
    Range("D100").Activate
    Selection.TextToColumns Destination:=Range("J3"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1))
    Range("E3:E64").Select
    ActiveWindow.SmallScroll Down:=-45
    Range("E3:E100").Select
    ActiveWindow.ScrollRow = 1
    Selection.TextToColumns Destination:=Range("L3"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1))
    Columns("H:L").Select
    Selection.NumberFormat = "0"
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    ActiveWindow.ScrollColumn = 3
    Columns("M:M").Select
    Selection.NumberFormat = "0"
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Range("N3").Select
    ActiveCell.FormulaR1C1 = "=RC[-6]/RC[-5]"
    Range("O3").Select
    ActiveCell.FormulaR1C1 = "=RC[-5]/RC[-4]"
    Range("P3").Select
    ActiveCell.FormulaR1C1 = "=RC[-4]/RC[-3]"
    Range("N3:P3").Select
    Selection.Copy
    Range("N4:P100").Select
    ActiveSheet.Paste
    Columns("N: P").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "0.00"
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Range("N3").Select
End Sub

Gill

[Simon]

Bloody hell!!  I'm impressed!  

[Clive]

Sam will probably respond overnight.  All he's doing these days is bleating that he's overworked teaching undergrads.  He doesn't know what a proper day's work is!!  Now when I was a lad, working down pit 18 hours a day, no holidays and earning just a shilling a week.....

[sam]

eh!! I was actually not moaning (well not yesterday)! In fact I was out last night having a good time with the undergrads as I am one of our physics society reps....this resulted in some rather interesting pictures being taken of me (I will have to show you).

Anyway back to the problem at hand, if that works use it! Cool. You could also automate the process by just selecting the column and changing the whole properties?

Yes, that works if you know that the fraction happens to be 2/7.  However, I'm looking to find a way to automate the process for columns of many different fractions without having to type in each number manually.

I read this, now I'm confused. Where are these fractions coming from?

[GillE]

Hi Sam

They are coming from bookmakers' websites .  I realise some (most) bookmakers give their odds in decimal form, but that's not the case with all of them.  Anyway, I find it easier to visualise the odds if they're written as fractions; I then let the computer manipulate the data in decimal according to my instructions.

I just enjoy looking at different approaches to betting.  I'm not a gambler myself; I've never bought a lottery ticket and I wouldn't know how to place a bet with a bookmaker.  The last time I actually 'bet' was in 1985 when I won the jackpot on a fruit machine and resolved to quit while I was ahead .

A couple of years ago I was examining horse racing data and found that there were a number of races that seemed to produce spurious results.  A few months later it transpired that a number of these races were being investigated by the police for fraud.  Perhaps this sort of stuff isn't everyone's cup of tea, but I find it interesting.

Gill

[sam]

i'd agree about it being interesting and heck at times that the only way i could cope working in a bookmakers.

The bookie always wins, trust me, I worked at labrokes for about a year. there are always some rather interesting results and I did for a while think that some must have been fixed. This angered me. I got to know, in some cases quite well, the people who came to my shop.  some were complete and utter addicts, others did it for the fun of it (or to make money in a way that they knew they could lose the money and still be ok) and it used to really p**s me off if i though something odd had occured.

oh by the way my idea with changing the whole row would work and you could quite easily do =sum(A CELL) into the corresponding row and have that in decimal form.