Search Not Just Numbers

Tuesday 25 June 2013

Excel Tip: Display and use fractions in Excel

I recently created a spreadsheet for tracking gambling debts for a client who wanted to enter (and display) odds as a fraction.

This required me to come up with a formula to read the fraction so that it could be used in the calculation of the winnings.

This got me to thinking that there will be other scenarios where it is preferable to enter numbers as fractions rather than decimals - and to display them as such.

So I thought I'd share my approach.

The easy bit is to get it to allow the user to type in a fraction and continue to display it as such. Simply change the number format of the input cells to Text, so that when the user types 1/4, that is what is displayed. A general format should also do this but it is better to format exactly what you are after rather than use General and let Excel decide.

The tricky bit is to get Excel to convert this to something it can use in calculations - i.e. a decimal.

The method I used was to use text manipulation formula to split out the components of the fraction.

The functions needed are VALUE, LEFT, RIGHT, LEN and FIND.

RIGHT, LEFT and LEN are all described in an earlier post:

Excel Tip: Manipulating text in Microsoft Excel

so I won't described them again here.

The two functions not covered in that post are:

VALUE(text) converts a number formatted as text into a number that Excel can then use in calculations. My first attempt was to use this to convert the fraction straight to a decimal number, however it requires the text to be a single number - not a formula. It can, however, be used to convert the numerator and the denominator into numbers.

FIND(text to find,text to search in,optional starting position) returns the position (as a number) of the first instance of the string text to find, in the string text to search in starting at the optional starting position if specified. e.g. FIND("D","ABCDEFEDCBA",5) returns 8 as this is the position of the first D it finds from position 5 onward - whereas FIND("D","ABCDEFEDCBA") returns 4 as it starts looking at the start of the string.

We basically want to return the value of the text before the slash and divide it by the text after the slash, to give us a number we can use. We will assume the fraction is entered in cell A1.

In both cases we will need to find the position of the slash, using =FIND("/",A1).

We can return the text before the slash by using the LEFT function:

=LEFT(A1,FIND("/",A1)-1)   (without the -1 we would return the slash as well, which we don't want)

We can then convert this to a value using the VALUE function:

=VALUE(LEFT(A1,FIND("/",A1)-1))

We can return the text after the slash by using the RIGHT function, but we will need to use LEN to know where to stop:

The number of characters we will need to return in the RIGHT function, can be calculated as follows:

=LEN(A1)-FIND("/",A1)

So the RIGHT function then reads:

=RIGHT(A1,LEN(A1)-FIND("/",A1))

We can again use the VALUE function to convert this to a number:

=VALUE(RIGHT(A1,LEN(A1)-FIND("/",A1)))

Finally, divide one by the other to convert the whole fraction to a usable number:

=VALUE(LEFT(A1,FIND("/",A1)-1))/VALUE(RIGHT(A1,LEN(A1)-FIND("/",A1)))

Definitely a good idea to put this into a working cell, so that you don't need to do this every time you want to use the fraction. All of your formulae can then refer to the calculated cell.

I'm sure some of you will have other approaches which I'd love to hear about in the comments.

Click here for our our exclusive offer on Online Excel Training

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

7 comments:

  1. Hi Glen

    Ingenious solution, but is it not easier to use the DOLLARDE and DOLLARFR functions to get what you want?

    Regards
    Andrew

    ReplyDelete
    Replies
    1. Thanks Andrew, I'd never come across those, but I don't think either would do exactly what I was after for the odds in this example. Good to know they are there though.

      Delete
  2. You could use the custom number format "# ???/???" to display the fractions.

    ReplyDelete
    Replies
    1. Another one I hadn't come across, and I'm sure I will use that for a number of things, however again it doesn't do what I needed in this case. The main problem with it for this purpose was that it simplifies it down to the lowest common denominator, so for example, odds of 10/1 would be shown as 10.

      Delete
  3. You can show and enter fractions in a cell by defining its format as FRACTION. No need to convert the value as its held as a decimal value in the cell but shows it as a fraction. Simples!!!!!

    ReplyDelete
  4. This worked great for me! I didn't actually want to use display as fractions, but as casino odds, e.g. "2 to 1". I adapted the formula and it works great!

    ReplyDelete
    Replies
    1. Pleased you found it useful Jeff. It was betting odds that led me to it too!

      Delete