Search Not Just Numbers

Thursday 22 December 2011

Merry Christmas - Take some time to stop and think!

Just a quick blog post to wish everyone a very Merry Christmas and a Happy, Healthy and Prosperous New Year.

And, before you go, here is a Christmas-themed video from BMW that also, I think, illustrates quite nicely what happens if you fly around at break-neck speed without stopping to think about whether you might be able to do things in a better way:




I hope you all have a great break and get the chance to stop and think. See you in 2012.


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Monday 19 December 2011

Accountants - A new way to improve your Excel use

In my last post I mentioned a new service that I was about ready to launch that would help accountants in practice to significantly improve their use of Excel, and therefore their profitability.

Well, Excellent Accountancy is now live with an amazing offer for those who act before Christmas.



If you're a UK-based accountant in practice, you really need to look at this as it could seriously improve your profitability in the New Year.

Excellent Accountancy Christmas Offer

Sorry for the unusually 'salesy' post, but this is a new service (and offer) that you really need to know about. For those of you not in practice, or in the UK, normal service will be resumed in my next post.


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Friday 9 December 2011

Apologies and Missed Opportunities

First of all an apology for such a long delay without a post. I have been working on a new service for accountancy practices to help them get the best out of Excel which has been taking up a big chunk of my time.

I can't say a lot about it at the moment, but for those of you who run accountancy practices in the UK, keep your eye out for the launch in the next few days as there is an introductory offer you won't want to miss.

Speaking of missed opportunities, here is a video I thought you might like. It charts the early stages of the internet through the eyes of a man who missed the opportunity to be at the heart of it:





If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Tuesday 9 August 2011

Spreadsheet help and assistance

I know many of you read this blog for Excel tips and will usually build your spreadsheets yourself, but I realised recently that you might not be fully aware of the other ways I can help with your spreadsheet requirements.

I have therefore added an additional page to the blog, see the new tab above - Need some help with your spreadsheet?

Whether you are after ongoing email advice when you're stuck or someone to build your spreadsheet for you, I can help.

If you have more involved requirements, such as automating your complete management accounts pack from your accounting system, I can come and work on site with you to achieve this.

Just drop me an email (glen@feechan.co.uk) if there is anything you need help with.


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Wednesday 27 July 2011

EXCEL TIP: Eliminating #DIV/0! and other errors automatically using ISERROR

Excel is great for being able to apply the same calculation consistently by simply copying the formula to all of the relevant cells, however often there are times where some of the data isn't how we would like it and the formula throws up an error.

The most common of these is the #DIV/0! error that Excel returns when trying to divide a number by zero. As Excel struggles with the concept of infinity it returns this error. A common situation where this occurs is in a Gross Margin Percentage calculation. We may have multiple products with sales and profit figures and a formula to calculate the margin as a percentage, i.e. profit/sales formatted as a percentage.

This will work fine until you come to a product with no sales where the formula will return #DIV/0!.

This can be addressed using a combination of the IF statement and ISERROR (note that in Excel 2007 and 2010 there is a combined function called IFERROR, however the solution proposed here will also work in earlier versions of Excel). If you need a refresher on how the IF statement works take a look at :


If cell A1 contains the Sales figure and B1, the Profit, then to show the Margin Percentage in C1, we would format it as a percentage and use the following formula:

=B1/A1

This will return the #DIV/0! if cell A1 is empty or zero.

The ISERROR function returns TRUE if its argument returns an error and FALSE if not, therefore

=ISERROR(B1/A1)

will return TRUE when this #DIV/0! would occur.

We also need to decide what we want to appear instead of the error. In this scenario, I usually show 0% which is what I have assumed for the example.

Using the ISERROR formula above as the condition for the IF statement, we can return zero if it is an error, or the original calculation otherwise. So C1 becomes:

=IF(ISERROR(B1/A1),0,B1/A1)

No more #DIV/0! errors!

A similar approach can be used for any other common errors, such as when a looked up value is not in the list.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Monday 18 July 2011

EXCEL TIP: Using the SUMIF function to interrogate lists

How many times do we need to pull out a particular category of numbers from a list and add them up. Well, you might not be aware that Excel has a pretty simple function designed to do just that.

The SUMIF function can be used to pull out departmental information from a trial balance, sales information from a transaction list for a particular salesman, department or division (as in the example we will use), or for any other situation where you have a list and need to selectively sum a subset of that list based on criteria.

It is actually a pretty simple function when you know how it works, but is very powerful in the types of situations outlined above.

See below the example situation we will be using to illustrate this function:


This is a simplified situation where we have a list of sales transactions in columns A to C (maybe imported from your accounting software) and want to report total Retail and total Wholesale sales.

We will use SUMIF to return the values in cells F3 and F4.

The format of the SUMIF function is as follows:

=SUMIF(CriteriaRange,Criteria,SumRange)

where CriteriaRange and SumRange are one column wide and the same number of rows as each other in height.

CriteriaRange is used for the column that includes that data that you wish to filter your sum by (in our example, C2:17).
Criteria identifies which item in the criteria range column you want to sum (in our example, "Retail" or "Wholsale" for cells F3 and F4 respectively).
SumRange is used for the column that holds the data you actually want to add up (in our example, B2:B17). This argument is optional and if not entered CriteriaRange is used.

So for our example, cell F3 contains:

=SUMIF($C$2:$C$17,E3,$B$2:$B$17)

Notice I have used the dollar signs to fix the ranges and used the reference to cell E3 rather than the word "Retail". This can then be copied down to cell F4 to become:

=SUMIF($C$2:$C$17,E4,$B$2:$B$17)


The criteria does not need to be a simple matching as in our example. A condition such as ">0" could be used to sum only positive values (this is an example where you might not enter the separate SumRange as the criteria is applied to the data being summed.

With our example data, the formula:

=SUMIF($B$2:$B$17,">1000")

returns £1,431.

Have a play with it and see what you can use it for.

SUMIF is great if you know the exact format of the output that you want and you require a fixed layout. If you want to summarise all of the data in your list dynamically, a pivot table might be more what you need.

If you want this kind of help on hand when you need it, have a look at my Excel Advice by Email service which provides email advice when you get stuck for a small subscription.


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Wednesday 6 July 2011

Need a Spreadsheet?

Many readers have used my Spreadsheets by Email service when they have wanted a spreadsheet built to their requirements, so I thought I had better ensure that readers were aware of some significant changes to the presentation of the service (no changes to the quality of the work, or who carries it out).

As the service is very useful both to those who know Excel well and need a little help (I suspect this is the case for most readers of this blog), and people who have never opened a spreadsheet in their lives - I was concerned that the website might have intimidated some of this second group if it came across too "geeky".

So, I've gone for a complete change of name and presentation, which I think will appear more accessible to all. And now you know where I've disappeared to for the last few weeks.

Welcome to needaspreadsheet.com.

Please take a look and let me know what you think, and if you have a spreadsheet you need building, please fill in the form on the site.


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Friday 3 June 2011

A business plan from my 12 year old son!

I received the following email from my 12 year old son this morning. I was at work, and he was home, obviously putting his half-term holiday to good use!


Subject:  I know how to make over £30.00 at school in 6 weeks
"Everybody is interested in tech decks at school and they all say the cheapest place to get them is crazy clearance. They are apparently £3.00 each at crazy clearance. I have found a pack of 10 tech decks for £14.43 which is about £1.44 each. If I sold 10 per week at £2.00 I would earn £20.00 and make a profit of £5.57 per week. If I could do that for 6 weeks I would have made a profit of £33.42 towards my TV. Is this a good idea. Also do you think offering a free lollipop from the shop would be a good idea if I couldn't sell any in my first week because I would still make a profit on each tech deck. In east lee stores the lollipops are 20p and were popular at the beginning of the year, being sold at around 30p. If I charged £2.00 for a tech deck and a free lolli It would cost me £1.64 per tech deck and I would make a profit of 36p per sale. I would not do this straight away however because without the the lolli I would make a profit of 56p per sale. That is a 28% profit margin which is quite decent. Please tell me if this is a good idea. The only thing is allot of people are selling used tech decks for £1.50 but with mine they would be paying for the quality. I think it is decent because it means more than doubling my starting budget which would have to be £15.00. Tell me if this is a good idea and if so could you please build me a spreadsheet when you have the time just to keep you practicing your spreadsheet skills. And don't charge me for the spreadsheet because I can do it myself."
Putting aside the typos and lack of paragraphs (and the cheeky jibe about Spreadsheets by Email at the bottom), I realised I had come across so many business plans from grown-ups that weren't as considered.

He has sourced a supplier, researched the market (even establishing that he can still cover his costs at the current rate for second hand product) and knows his margins. He has even given thought to a promotion!

Part of me feels incredibly proud, and part of me worries I've created a monster!

By the way, I had to ask what a Tech Deck was, and this was his reply:
"It is a mini skateboard that you can use your fingers to ride and do tricks with. You can also get mini skate park equipment to use it on. Tech deck is the brand name though. you can get cheaper ones but they break alot easier."
What's more, this second email had a link to his blog in the signature!

It sounds like he'll do his bit for all of our pensions!

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Friday 27 May 2011

Working over the weekend?

We're about to finish for the Whitsun bank holiday weekend here in the UK, and I'm curious how many of you are going to be working over the weekend. Or what would be more interesting is how many of you will actually get a weekend completely free of work (for those of you not in the UK, will you get Saturday and Sunday?). That means not checking work emails - completely switching off from work.


I am aiming to spend some time with the family, maybe get out for a long walk in the middle of nowhere, but I would struggle to not check emails on my phone when I have a signal, and have some work I definitely need to fit in over the weekend.

Please let me know what you are up to in the comments. For those of you who are not even checking emails, maybe you can answer on Tuesday!



If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Friday 20 May 2011

EXCEL TIP: The dollar sign ($) in a formula - Fixing cell references

I want to cover something today that I use all of the time but seems to be understood in varying degrees by clients I work with.

I am talking about use of the dollar sign ($) in an Excel formula.

Relative cell references
When you copy and paste an Excel formula from one cell to another, the cell references change, relative to the new position:

EXAMPLE:

If we have the very simple formula "=A1" in cell B1 it will change as follows when copied and pasted:

Pasted to B2, it becomes "=A2"

Pasted to C2, it becomes "=B2"

Pasted to A2, it returns an error!

In each case it is changing the reference to refer to the cell one to the left on the same row as the cell that the formula is in, i.e. the same relative position that A1 was to the original formula.

The reason an error is returned when it is pasted into column A, is because there are no columns to the left of column A.

This behaviour is very useful and is what allows a sum to be copied across or down the page and automatically refer to the new column or row that it finds itself in.

But in some situations, you want some or all of the references to remain fixed when they are copied elsewhere.

The dollar sign ($)
This is where the dollar sign is used.

EXAMPLE:

Take an example where you have a column of Sales values in Pounds Sterling in column A and a formula to convert these into US Dollars in column B. You could enter the actual exchange rate into the formula but it would be more sensible to refer to a cell where the exchange rate is held, so that it can be updated whenever it is needed.


The simple formula for cell B2, would be "=A2*E1", however if you copy this down, then the formula in cell B3, would read "=A3*E2" as both references would move down a row as described above.

This is where the dollar sign ($) is used. The dollar sign allows you to fix either the row, the column or both on any cell reference, by preceding the column or row with the dollar sign. In our example if we replace the formula in cell B2 with "=A2*$E$1", then both the "E" and the "1" will remain fixed when the formula is copied. i.e. in cell B3, the formula will read "=A3*$E$1", still referring to the cell with the exchange rate in it.

In this example we have fixed both the row and the column, but in other situations, you may just want to fix one or the other, for example:


Above we have a spreadsheet calculating the times tables where we want to every cell in the white area to be the product of its row and column heading. This is easy using the dollar symbol. In cell B2, the formula without dollars would be "=A2*B1", but for this formula to work when copied to each column, we need it to always look at column A for the first reference and to work for each row, we need to always look at row 1 for the second. Using the dollar sign to do this, it becomes "=$A2*B$1". This can then be copied to every cell in the white area.

Quick Tip
You can speed up entering the dollar signs by using the function key F4 when editing the formula, if the cursor is on a cell reference in the formula, repeatedly hitting the F4 key, toggles between no dollar signs, both dollar signs, just the row and just the column.



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

A New Era for Not Just Numbers

Sunrise over Seaham
Hello again to regular readers who might be wondering where I've been. No blog post for over two months! Well, I've been busy reviewing how all the various parts of my business fit together - including this blog.

Not Just Numbers was originally intended to generate traffic for my other businesses, however it is difficult to justify the effort required purely on this basis - hence the deliberations over the last couple of months.

I have been wrestling with this because I enjoy doing the blog and would hate to give it up, and there are many intangible benefits which are difficult to measure for both the business (additional credibility for clients brought in through other routes for example) and for me personally (communicating with many interesting readers that I would not have otherwise met).

Well, the deliberation is over and I have decided not only to continue, but step up the blog activity, while looking for additional ways in which the blog can contribute financially (I still have a business to run!). The most immediate change you will see is the donation button in the right hand column, but I will also be looking for additional advertising where appropriate.

So, onwards and upwards.

PS: Not Just Numbers posts are like buses, none for two months then you get two in one day - there will be another post later today about using the dollar sign in Excel formula to fix cell references.


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Friday 11 March 2011

Automating regular procedures (e.g. month-ends) - the real benefits

Ah, month-ends, those joyous occasions where we get to reflect on the successes of the last month's results and toast the even better ones to come....

What! Not your experience of month-ends?

How about a desperate panic of late nights trawling through those familiar journals and spreadsheets to hopefully pull together a set of management accounts that makes some kind of sense?

Does that sound more like it?

When we finish, we pause briefly, dribble a bit, think we must be able to automate some of this, and then start gearing up for the next one.

If this is you, you might want to consider what the real costs are of not finding the time (or money) to automate some of this.

What are the costs to the business?

  • Missed business opportunities - A finance department focused on producing the numbers instead of interpreting them can cost a business dear. If the finance department spent the time lost to month-end procedures on reviewing the month-end numbers and understanding their implications, the business could benefit greatly;
  • Missed business problems - For the same reasons as above, this pre-occupation with getting the numbers out can lead to future problems being missed until it is too late;
  • Increased staff turnover - The extra workload and pressures (and late nights) can mean it is hard to hang on to good employees in the finance department;
  • Less accurate accounting - Accounts produced in this environment rarely have time to be properly reviewed, allowing errors to slip through the net.

But what about the cost to you?
  • Stress - It goes without saying that this working environment is not conducive to low stress levels;
  • Lack of career progression - Spending all of this time working on what has to be done, stops you working on those areas where you could add real value to the business, and demonstrate your worth. The pay rises come a lot easier when the boss sees you as generating profit rather than as a necessary cost;
  • Time - The biggest cost of this type of approach is time. A chunk of that time put into repetitive month-end procedures could be directed into adding value to the business (as discussed above), however there should also be a significant chunk available for you, particularly if the job currently involves a lot of work outside of office hours. You could start going home at 5pm and not coming in at the weekend. Just think what you could do with that time:
    • Spend more time with the family;
    • Play more golf;
    • Take up a hobby;
    • Learn to fly;
    • Become an internet millionaire (I keep reading emails saying I can do this in 4 hours a week!).
I hope this has made some of you spend a little more time this month on the "we must be able to automate some of this" part of your month-end.

If you want to discuss how you might go about it, drop me an email (glen@feechan.co.uk).

Here's to making month-ends fun!


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Wednesday 26 January 2011

How long have you been using Excel?

I came across this short video celebrating the first 25 years of Microsoft Excel and it got me to reminiscing about my early days with spreadsheets.



I didn't start using Excel myself until 1998 when the company I was working for went through a management buyout and I decided to do the forecasts for the post-buyout company in Excel rather than Lotus-123, and bring the rest of the accounts department across afterwards. This put ridiculous pressure on me to convert the management accounts into Excel immediately after we completed the buyout. This is when I came up with the basis of the approach I outlined in my first ever post on this blog, back in September 2008 (read it here).

I first used Lotus-123 during my 3 years training in an accountancy practice (1992-1995) on an ancient 'portable computer' that looked like a sewing machine and was shared by all of the accountants in the practice. I remember it had orange text on a black background and we only really used it for Fixed Asset Registers!

Would love to hear any stories of early Excel (or Lotus-123) use from any of you. Please share in the comments below.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report.

Tuesday 18 January 2011

Just for fun - How I created my Excel Christmas Quiz

Before Christmas you may have seen my Excel Christmas Quiz which you can still download here (although the questions might no longer be appropriate for the season, the prize is still valuable until the end of January).

I thought it might be useful in this post to tell you how it was made.

This post will make more sense if you download the spreadsheet first.

The Quiz

The quiz element is pretty straight forward. There are 3 hidden columns alongside the spaces for the answers. One column contains the correct answers (column T), another column (column S) contains a nested IF statement as follows:

(for the first question - row 5)  =IF(R5="","",IF(R5=T5,"Ho Ho Ho","Grinch!"))

which returns a blank cell if no answer is entered, returns "Ho Ho Ho" if the answer entered is equal to the correct answer and "Grinch" if not. Cheesy I know, but it was Christmas.

The third column (column H) contains another IF statement:

(again for the same row) =IF(R5=T5,1,0)

which returns a 1 if the answer is correct or a zero if not.

This third column is summed for the score as follows:

=SUM($H$5:$H$14)&"/10"

This takes the sum of these 1s and 0s and adds the text "/10" after it. See my post on manipulating text in Excel for more detail on this.

The revealing of the Christmas picture


This was done using the background facility in Excel and a series of IF statements combined with offset formulae and conditional formatting.

I first set the background image to be the Christmas picture using Format -> Sheet -> Background. As this tiles the picture in the background and gives no facility to re-size I needed to use some trial and error to get the size I wanted and had to have the image in the top-left to ensure it stayed in the right place.

I then needed to choose a background colour for the rest of the sheet so that the picture was not repeated across the whole sheet - for this I simply formatted the cells with that fill colour (in my case green). It is easier to apply this for the whole sheet and then remove the colour from the cells that hide the picture.

I then created on a separate sheet (hidden) a grid of the same number of cells as the picture, containing random whole numbers between 0 and 9 in each of the cells, representing the 10 questions. This will be used to determine which cells are revealed for each question. I actually used the random number generator to create this grid using the following formula:

=ROUNDDOWN(RAND()*10,0)


RAND() returns a random number between 0 and 1, multiplying this by 10 therefore returns a random number between 0 and 10 (but not necessarily a whole number). The ROUNDDOWN formula rounds down to the number of decimal places specified in the second argument (in this case 0). The result of the whole formula, therefore, is a random whole number between 0 and 9.

Because the RAND formula recalculates on each calculation the spreadsheet does, I needed to fix these numbers as I wanted the cells revealed by a question to stay revealed, not be re-scattered across different cells every time an answer is entered. The simplest way to do this was to copy the grid of numbers and re-paste it over the top of itself using 'paste as values'.

Going back to the main sheet I entered the following formula in the top-left cell of the picture (A1):

=IF(SUM($H$5:$H$14)=10,"",IF(OFFSET($H$5,Sheet4!A1,0)=1,"",1))


The objective of this formula is to return an empty cell if the corresponding question is correct and a 1 if not. I'll explain shortly what we do with this result.


The first IF statement returns an empty cell if the sum of the scores is 10, so all cells are empty if all answers are right. If the total score does not equal 10, the second IF statement checks whether the score of the relevant question is correct (i.e. 1 in column H) and an empty cell if not. The offset function is used to determine which question to look at. $H$5 is the first score (with dollars to fix this reference) but the row number is offset by the number in the corresponding cell in our number grid (on Sheet4). The zero is the number of columns to offset. For example, if the number in the grid is zero, the offset formula will look at cell H5, i.e. the score of question 1, if it is 9 it will look at cell H14 (5+9) and return the score for question 10.

This formula is copied to all cells on the picture, leaving the dollars off the Sheet4 reference so that in cell A2, it looks at cell A2 on Sheet4, etc.

We now have empty cells corresponding to correct questions, which is right as we want an empty cell with no fill as the picture is revealed. For incorrect, or not yet completed, answers we have a 1 in the cell. We use conditional formatting to use this result to hide these cells.

First of all, set the font colour of these cells to the background colour of the rest of the sheet (green in this case). We now have a green "1" in front of the picture for incorrect answers.

We use conditional formatting to choose a green background for these cells if they contain the number 1.

In Excel 2003, select Format -> Conditional Formatting, in Excel 2007/2010 you need to add a new rule. In either case select < Cell value is > < equal to > < 1 > for the three boxes and click the format button to choose the green fill.

Now the picture has a green background until correct answers are entered to gradually reveal the picture.

Revealing the prize


The final step is a much simpler version of revealing the picture, that is only dependent on the total score.

The background of the prize box is set to yellow using the normal cell format and the font colour to whatever you want it to be when it is revealed.

We then use conditional formatting again but enter the following for all of the cells in the prize area:

Formula is > < =$R$16<>"10/10" >

setting the format to a yellow font colour. This switches the font colour to yellow if the score in cell R16 does not equal 10/10, making the text invisible on the yellow background.

Protecting it all

Finally, we need to protect the sheet so that none of this information can be seen or edited.

Cells are formatted as "Locked" by default, so we need to select the cells that the user needs to be able to use (just the 10 answer boxes), select Format->Cells and on the Protection tab, un-tick the "Locked" box.

We then protect the sheet as follows:

Select Tools->Protection->Protect Sheet

Enter a password so that users cannot switch off the protection and un-tick all boxes in the list except the one that says "Select unlocked cells". Once you have clicked OK, the user cannot even click in any of the cells apart from the 10 answer boxes.

There you have it! Your very own Excel Quiz.


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report.

Tuesday 11 January 2011

JK Rowling on the benefits of failure

Happy New Year to everyone.

Sorry for being a bad blogger! It's 11th January and I am just getting round to my first post of the year. Even then I'm still catching up so it is a short one just to show you a video I came across today. I certainly found it both entertaining and inspiring - some of you may have seen it before.

In the current economic climate, I am sure that many of us might feel that we are failing in some way. In this video of her Harvard Commencement address from 2008, Harry Potter author, JK Rowling talks about how important failure was to her success.

I hope you enjoy.


J.K. Rowling Speaks at Harvard Commencement from Harvard Magazine on Vimeo.


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report.