SME Blog

My favourite Excel function

I'm a self confessed Excel junkie.  I've tried other spreadsheets, but to me they just don't have the flavour of the real thing.



The Excel vlookup function is rather useful.  If you look above you can see it in action. 

In this example, we have a table of sales budget figures for each month (in green).  By changing the month number in cell D2 (in red) it gives the corresponding sales figure for the month in cell D6 (in blue).

To do this all that is required is the vlookup formula as follows:

=VLOOKUP(D2,F3:G14,2,false)

So what does that actually do?  The function looks up the value in D2 and looks for it in the first column in cells F3:G14.  It then returns the corresponding value in the second column.  Our our example the second column is the sales budget column.  Adding "false" just ensures that Excel looks for exact matches.

Our example is quite simple, but it does give a taster for what is possible with the vlookup command

Spotting the trends

Google Trends is a fascinating free tracking tool.  The EEE PC is currently getting a lot of attention in the UK so I will use that for our first example.  Google Trends gives the first graph below.  This shows the searches for a chosen term relative to the total number of Google searches as displayed over a period of time.   If we look at the graph Google Trends creates then we can see that the EEE PC is currently a hot topic.

Now let us look at the Google Trend data, but this time compare three different topics.  I'm going to pick up on Microsoft's current interest in Yahoo and use Microsoft, Yahoo and Google as my search topics to give the second graph below.

One look at this graph reveals why Microsoft are so interested in joining of forces with Yahoo.  Of course Google Trends could be viewed as a toy and Google do give some disclaimers on it, but it does show a side of online technology that I believe will come more to the fore in future.  The politicians at the very least will always want to track the trends.  Try it for yourself at www.google.com/trends.

ICAEW launches IT Counts - a world first?

A new online network has just been launched called IT Counts.  Whilst you may be familiar with Facebook or Myspace this one is different.  What makes IT Counts different is that this online network is provided by the Institute of Chartered Accountants in England and Wales (ICAEW) and Microsoft.  If you thought that the words innovation and accountant never belonged in the same sentence then it is time to think again.

ICAEW IT Counts is focused on practical business IT.  The site is aimed at the general individual. It should also interest the technophile as IT Counts sets itself apart from the flood of technology sites with its strong business slant.

You may be wondering why Chartered Accountants need an online network dedicated to business IT.  The answer is simple.  The Chartered Accountant is no longer the stereotypical bean counter and an awareness of technology is a pre-requisite in a modern business environment.

IT Counts is packed full of Web 2.0 technology and uses the Wordframe platform. I've been lucky enough to be involved with the ICAEW's online network development together with Dennis Howlett, David Terrar and many forward thinking individuals at the ICAEW (more on that later).

The initial launch is restricted to ICAEW members, but that is expected to change.  This leaves the enticing prospect of the ICAEW as a professional member organisation hosting a virtual room that includes itself, Microsoft, CA's, technology specialists together with other business professionals and owners.  I  may be wrong, but as far as I'm aware that will be a world first.

Workflow using stickies

Most small companies act as a team rather than a machine. When a small business grows to a mid-sized business then workflow starts to become a serious issue.  Tasks will be naturally delegated and the business owner starts to become more detached from the everyday detail.  There is a danger point in losing control and inefficiencies can easily occur as it becomes more difficult to coordinate business processes.

If you can think of one process in your business that you particularly need to control and coordinate then it may be time to take the stickie test.  The stickie test is a keep it simple approach using small paper stickies.  As an example we will take the process of producing a report for a client.  On each stickie you need to put the step in the process and who is doing it.  In our example, we would have the following steps:

1.    Creating the report - Mr Creator
2.    Reviewing the report – Mrs Reviewer
3.    Second review of the report – Mr Signoff
4.    Delivering the report – Mr Signoff

We have defined the process steps and the order.  A quick look at the process and we can see there may well be some loops in the process where the work goes back to Mr Creator for amendments and changes.

You may well find your particular process is far more involved than our example, but just creating stickies as above will give a quick insight to improve a process and make it more efficient.

Work life balance

In the words of one entrepreneur:

"Yesterday I was sick as a dog. But my business partner was out of town, so I had to go in to work. I was supposed to have a nice steak dinner with my wife that night. All I really wanted to do was curl up by myself on the couch..."

The above quote comes from Jeff Weinstein in an article in BusinessWeek.  Jeff is trying to solve the problem of the work life balance.  Good luck to him.  My hand is up, this is one problem I can't ever seem to quite crack.

Life can be incredibly rich. I find there isn't enough time to do everything well and make it fit neatly into one box.  Jeff however thinks he has cracked it.

He's tried all the traditional methods such as budgeting time and not bringing work home, but that has not worked.  He is now taking on the 21st century solution of being flexible and realising that work is no longer 9 to 5.  Now Jeff takes some leisure time during the 9 to 5, but works in the evening when required.  The odd email or phone call at 8pm shouldn't be a problem.  He discusses his work much more with his wife in the evening.  By the end of the day he's completed all his tasks.  Eureka! It is early days but Jeff thinks he has cracked the work life problem. The 21st century approach of flexibility works after all?  Not a chance.

The reason it is working for Jeff is that it is early days.  He has not yet established a pattern of working in the evening.  Once he does he will also establish an expectation on the part of the people he is working with.  Soon they will be expecting that Jeff respond in the evening and will be disappointed when he doesn't.  Work can be a bit like traffic sometimes.  It fills up a new motorway or freeway almost instantly.  I hope I'm wrong, but I expect a return visit to Jeff in two years will see him working harder than ever.

I remember watching Tomorrows World in the 1970's and being given the message that we all have more leisure time in the future.  It hasn't really turned out that way and that message has been well and truly trashed.

From the customer point of view this is of course good news.  Why can't the customer have instant response and extended hours.  It is a benefit that Jeff is available from, say, 8 to 8.  Why shouldn't a customer get this service? Like it or not that is the way the world is moving and the work life balance problem is becoming larger.  It is one problem that SME Blog certainly wants to crack.