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".

Tuesday, 18 June 2013

Art, Gaming, Rock and Roll - What do you use Excel for?

An old friend from school (Yin Maher), shared this picture with me on Facebook - because it was amazingly made using Excel Autoshapes by 73 year-old Japanese artist, Tatsuo Horiuchi.

You can see more of Horiuchi's work, and even download the spreadsheets at the following link:

Tatsuo Horiuchi | the 73-year old Excel spreadsheet artist

Apart from contemplating on what it says about me that girls from school associate me with spreadsheets - it also got me thinking about other uses of Excel that Microsoft could never have conceived of.

One of the more obscure uses I have seen is the recreation of AC/DC's Rock and Roll Train video in Excel.

Another use, that stretches the boundaries of Excel is Canadian accountant Cary Walkin's creation of an entire fantasy video game in Excel.

So, what bizarre uses have you seen Excel put to? I'd love to hear about them 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".

Tuesday, 11 June 2013

Excel Tip: Freeze panes to make scrolling easier

Another simple tip this week. This time to make life easier when scrolling around large spreadsheets.

Many of you may know this, but I regularly see users scrolling around large sheets, lost - or using split windows as a very clunky way to try to achieve the same thing - so clearly, many users don't.

So if you need to be able to know your heading and row titles wherever you are on a worksheet, read on.

Freeze Panes is accessed from the View ribbon in Excel 2007 onwards, or from the Window menu in earlier versions.

In the later versions, you get three options, Freeze Panes, Freeze Top Row and Freeze First Column. Once I have explained the first one, the second and third options will be pretty obvious (and don't exist in Excel 2003 and earlier as the same can be achieved easily using the first option).

Selecting Freeze Panes freezes everything above and to the left of the active cell. Meaning that these rows and columns will stay on screen when you scroll down or to the right, no matter how far you go.

For example, if you click in cell B3 and select Freeze Panes, this will freeze (or lock) rows 1 and 2 and column A. This means as you scroll to the right, the first column to disappear from view will be column B, and as you scroll down, the first row to disappear will be row 3.

If you no longer need these panes frozen, you will notice that the option on the menu has now changed to Unfreeze Panes, so just click that to remove the lock.

Simple, but can make life so much easier when a spreadsheet doesn't fit on your monitor screen.

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".

Tuesday, 4 June 2013

Excel Tip: A quick way to add dollar signs in a formula

Last week, I posted a really simple tip for copying down a formula to the bottom of your data.

This turned out to be one of my most popular posts, registering over 2,000 page views in under a week.

I said in that post:

I've lost count of the amount of times I've used this in front of an experienced Excel user who has stopped me and said "What did you just do there?".

Given its popularity, I thought I would post another simple tip that has regularly elicited that same response.

Coincidentally, this also relates to my most popular post ever (written two years ago, it still drew over 5,000 page views last month!), The dollar sign ($) in a formula - Fixing cell references.

If you don't know why you would want to add dollar signs to a formula, then I would recommend reading that post. If you do, read on for a simple, but often missed, tip for adding them quickly.

When entering a reference in a formula, there are four possible ways in which you can apply the dollar signs:

1. Fix both the column and the row, e.g. =$A$1
2. Fix just the row, e.g. =A$1
3. Fix just the columns, e,g. =$A1
4. Fix neither the column, nor the row, e.g. =A1

This can be quite fiddly, typing the dollars in the right place, particularly if you have entered the reference by clicking the cell and then need to click the cursor in the right place to enter the dollar sign(s).

This is where this simple tip comes in.

As long as the cursor is in the reference, or immediately before or after it, you can use the function key F4, to toggle through the options above (in the order shown).

That's it, pressing F4 once adds both dollars, twice fixes the row, three times fixes the column, four times removes the dollars again. If you got over-excited and missed the right one, you can keep cycling through the options until you hit it again.

Simple, I know - but again, only if you already know it!

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".