I'm creating a simple spreadsheet to calculate some betting odds and keeping track of my wins/losses
when I put fractional odds in one column Excel converts some of them to whole numbers (i.e. the ones that are 1/1, 2/1, etc where it does not do it for odds like 4/11, 7/2, etc.
Is there a way of turning this off?
Please note that some of the top heave fractions (11/2, 11/10 etc) get put into whole numbers such as 5 1/2 etc! And I do not want this to occur either
I've tried the Custom formatting of the cells but all of the denominators will inevitably be different, so having something like ??/28 won't work for me
EDIT:
This was solved using the custom format ??/?? and simply removeing the # that was at the front of the custom cell format dialog box
You simply need to change the cell format; you want to use ???/???. This will make Excel represent any decimal number to the closest fraction approximation it can find using the specified numerator and denominator significant digits (number of ? in the format string)
If the cell input is directly a fraction, it will reduce it if possible but always keeping the fraction format.
Examples:
= .10 will be converted to 1/10
= 0.1231 will be converted to 81/658 (supossing ???/??? format is used).
= 10/100 will be converted to 1/10
= 11/12 will remain as 11/12 as no reduction is possible.
= 1/1 will remain as 1/1
etc.
The behavior you are describing is becuase you are using one of Excel's default fraction formats which are all similar to # ???/??? (take note of the leading #). This format will reduce integral values to the non fractional part.