6/4 not 3/2

[scottymelloty]

Hi there,

This has really been bugging me for some time now, i work in the
sporting industry and work out odds on sporting events, to work out my
perecentages i format the cells as ##/## to enter my odds ie 5/4, 10/1,
13/8 etc but when i put in 6/4 or 4/6 it always goes to 3/2 or 2/3,
this is not a problem for me working it out but when i pass it on to
other departments they have to change it manually to 6/4 or 4/6.

The only way i can think of is to format it as text but then my
formulaes dont work becase it's not a number anymore.

Any Ideas ??

 

[Andy B]

Hi

You could try setting up some custom number formats: like 0 0/4 or 0 0/8
etc.
This will retain the fact that it is a number.

Andy.

 

[Dave Peterson]

AndyB gave an example of how to format the cell to show odds when you already
know what the denominator will be.

But if you used "0/4", then 6/4 would look nice, but 4/6 would look like 3/4.

I don't know of a way of doing this except for changing the formatting on the
fly with an event macro.

(Just curious why 3/2 is not equivalent to 6/4 as far as odds go, too.)

Are there rules that can be applied?

Here were my guesses:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim SavedNumberFormat As String
Dim myStr As String
Dim myDenom As Long

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a:a")) Is Nothing Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
If IsEmpty(Target) Then Exit Sub

With Target
SavedNumberFormat = .NumberFormat
.NumberFormat = "0000/0000"
myStr = .Text
myDenom = CLng(Right(myStr, 4))

Select Case myDenom
Case Is > 999: .NumberFormat = "0/0000"
Case Is > 99: .NumberFormat = "0/000"
Case Is > 9: .NumberFormat = "0/00"
'any more special ones?
Case Is = 3: .NumberFormat = "0/6"
Case Is = 2: .NumberFormat = "0/4"
Case Is = 1: .NumberFormat = "0/1"

Case Else
'leave it what ever was there
.NumberFormat = SavedNumberFormat
End Select

End With

End Sub

Right click on the worksheet tab that should have this behavior and select "view
code". Then paste this in.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

This actually just reformats the cell with a format of 0000/0000. Then it looks
at the denominator and bases the numberformat on what it finds.

 

[scottymelloty]

Thanks for the response guys, i think i'm going to have to go down the
macro route but i'm not really up on that so it may take me some time
but will have a go.

*Dave there is no difference between 3/2 and 6/4 for odds purposes but
people have got used to seeing 6/4 over the years, i dont know the
origin to be honest, maybe i should just set a new tradition and start
using 3/2 until people get used to that.

Many Thanks for the help

 

[Dave Peterson]

One person can make a difference--you can be the Gandhi of the bookie set <vbg>.

 

[Peo Sjoblom]

LOL!

--

Regards,

Peo Sjoblom

One person can make a difference--you can be the Gandhi of the bookie set