Global Warming? Could we have some of the warmth please?

I have been trying my very hardest to ignore the fact that it is July. Because I live in Ireland. And I used to live in South Africa. And I am pretty darn sure that their winter is probably a little bit warmer than our summer this year. Now don't get me wrong, Ireland has a lot of good things going for it. Lovely people. Lovely beaches. Lovely scenery. Just not so lovely bbq weather.

So I was wondering today whether I am being a bit thick-skulled. I mean, I have lived here for nigh on eight years. I should be used to the non-event that is summer. My husband talks fondly of his hot childhood summers in Cork. Even I remember a couple of good weeks of sunshine when I first moved here. So am I just being silly, wanting a few days of sun?

I don't like thinking I may be losing my mind, so I decided to do what all good analysts should. I graphed the data. This data is all from www.met.ie. So, from 2000 to 2012, the total rainfall and mean temperatures recorded in June at Cork Airport have been:

If we start by just doing a simple bar graph of the temperatures, it looks like this:

I have added a trendline, and just look at that. Things are definitely slipping. To add a trendline, double click on one of the bars to select your series, right click and select Add Trendline. You can change the look of the line through the options in the dialog box that pops up.

Now, we add in the rainfall, just for the laugh. If we maintain the graph as is, it will look like this:

Not so helpful. What we need to do is plot the rainfall on the secondary vertical axis. You need to follow a couple of steps to do this:
1) Select the rainfall series by either double clicking on one of the bars, or going to your Chart Tools tab on your menu ribbon and going to Format. At the top left hand side you will see a drop down box. Select the Rainfall series, like so:

2) Once the series is highlighted, right click and select Change Series Chart Type. Select a basic line graph.
3) Right click again and select Format Data Series. The following Dialog box will pop up:

Under Series Options, click on the Secondary Axis and you're sorted. Add another trendline for the rainfall, make your lines pretty, and your graph will look like this spectacular specimen below:

Thankfully, this graph proved I wasn't completely losing my mind. Temperatures are going down and rainfall is going up. Not a good recipe for planning bbq parties.

However, I would be a very bad analyst if I didn't point out that the ridiculous amount of rainfall in June 2012 is definitely skewing the trendline. So I re-did the graph up to 2011:

Looks kinda different, doesn't it? Rainfall is still on the way up, but the downward trend on temperature needed another year of low temperature to materialise. No wonder I expected a little bit of heat this year.

Now, unless you work at Met Eireann, these graphs may seem a bit useless. But using the secondary axis is one of my favourite ways of comparing sets of data that have very different number ranges. You can even use percentages. It sometimes really brings home the point you are trying to make with data, when you show not one, but two trends, telling the same story.

This week my newsletter is going to cover something completely different and a little bit fun...how to format your cell comments to make them a little bit more exciting than the drab yellow box. Change the shape, add in pictures. Interested? Sign up here.

Hope you have some sunshine in your spreadsheets this week!

Millionaires and their assets..can you link it up?

Close your eyes and imagine you're a multi-millionaire. Think of all those houses, holiday homes, cars, private jets.... When I do this, I just think that it must be an absolute nightmare to keep track of the asset values. (Ok, I may fantasise about a beach house in the Maldives...but I'd still worry about keeping my asset values under control!)

Back to your daydreams....now picture that your beloved wife blindsides you with a surprise divorce (who could I be referring to ?). Besides maybe needing a good friend on speed dial, you would also need to have those asset values to hand, and pretty darn quick. Imagine, too, that you don't mind having a large staff, and you don't want any one of your accountants having access to too many of your assets. To provide adequate segregation, you have an accountant deal with each of your asset groups, and each of them report to you directly with a summary of assets, values, and the current month's running cost.

* Note - this is a simple illustration, so these figures are completely and utterly fictional, as I have eschewed buying luxury houses or cars in lieu of a simple life. Also, I would assume any multi-millionaire worth his salt would want to know a whole lot more about his assets, but we're keeping this simple....

So how can I help speed up this little part of your working life? Well, imagine each asset accountant has a template which he updates each month:

You, sitting in your plush office, add these figures every month to an asset overview sheet you use to keep track of your investments. This admittedly wouldn't take long but, to make it even easier, you could link your workbooks so that when your accountants update their templates each month, yours would automatically update too.

So, how do we link workbooks? Easy really. There are 3 ways to choose from, which I will take you through below, but what you want is for your summary to have formulae that look like this:

All this formula is saying is, instead of looking for the total on the summary sheet, go to a different workbook (City Apartments.xlsx) and in that workbook, look for a sheet called "City Apartments", and then find cell C10 on that sheet and bring its contents back to the summary sheet.

The official syntax is [WorkbookName]SheetName!CellAddress

So, how do get the Linking Formula into your workbook?

1) You can type it in.
This may be an easier option if you don't have the other file open, but some things to note on typing in a file name:

 a) 100% accuracy is required otherwise it won't work;

 b) If the file is closed, you will need to type in the full address, which may look something like this; ='C\Data\Assets\Monthly Updates\[City Apartments.xlsx]City Apartments'!$C$10 . Not so easy to get 100% right (see point a);

 c) If your workbook or worksheet has spaces, it will need the little ' at the beginning and end of the name.

2) You can point and click
This really is as easy as it sounds. Firstly, open your Summary Sheet and City Apartments sheet. In the Summary Sheet, click on Cell C4 and press =. Then go to your City Apartments sheet and click on cell C10. Press enter and your formula is there, perfectly spelt and looking impressive.

You can use this for all sorts of formulae that require an external link, not just picking up a number. You just type your formula and when you need the external data, just include the external cell references as I described above.

This, in my mind, is the best and easiest way of setting up links. It allows you the most flexibility, and is always accurate.

3) You can paste a link

If you simply wanted to transfer data from one cell into your workbook, as we did in our example, you can just paste as a link. So, using our example, to transfer the total value from the Cars workbook, you would click on cell C11 in the Cars workbook and copy it. Then go to cell C6 in the summary sheet, and Paste Special - Paste Link (Or Home - Clipboard - Paste - Paste Link). This creates the same linked formula you would get by pointing and clicking.

With points 2 and 3, when you close the external workbook, the full name is then shown in your formula.

With all of the above steps, if the external file is updated, when you open the summary file, the data will update automatically.

Also note that in Excel 2010, new security feautures have been added. So the first time you re-open your Summary sheet, you will see a ribbon appearing at the top of your page asking whether you want to enable links. Click Yes, and Excel shouldn't ask you again.

Now, I hate to bring you back down to earth, but time to stop imagining you're a multi-millionaire, and start assessing whether there are any workbooks you can link, and therefore save you time.

Our newsletter this week will provide some more tips on link, and some troubleshooting hints. If you haven't already, why not sign up here?

Happy Spreadsheeting!

A PS here (thanks to reader Martin for pointing out that I forgot to mention this) - if you change the source file (ie - insert or delete rows or columns) , when your final file is closed, the final file will not be adjusted, and will pick up the wrong data. So either always have both files open when making adjustments, or use a vlookup to pick up your data.

Micro Mass, Body Mass and Macros.....

Well, the 4th of July 2012 was significant. Not only did the United States' celebrate their 235th day of Independance, but the good scientists in CERN announced that they are pretty much certain they have discovered the Higgs Boson. The "what?", you may splutter into your coffee. I am certainly no expert in particle physics, so here is an easy explanation. In short, it is the tiny little particle that gives everything mass.

This led me to think about my own body mass (not something I like contemplating for very long!), and the fact that I hate having to go online to use a BMI calculator, when it would be really easy to create my own, and track my progress. Which then led me to thinking about Macros. And how they can make life easy. And how they can also make life very very frustrating.

So I am going to show you how to record a VERY simple macro. This one is really very simple. But it will give you an idea of whether there is anything you could speed up if you just recorded a simple little macro to save you from doing repetitive and boring tasks.

However, it comes with a warning. It is my experience that if you want to play around with Macros, and experiment with improving your work life, you want to have someone close by who knows quite a bit about Macros. Because they are finicky little blighters, and they can come up with errors that are as elusive as the Higgs Boson itself. That said, don't let me put you off, because they can save time.

Another thing to note is that you can of course write Macros in VBA from scratch. You don't need to record them at all. Its just easier, when you are starting out, to record them.

So, the first step in recording a good Macro is planning. Make sure you have everything you need set up before you press record. So I have created a (very fictitious) set of numbers for our test subject, Fred. Fred is 1.85 metres tall and got a lecture in December from his doctor for having a BMI of 36.52, which is classified as clinically obese. Fred's New Year's resolution was to get his BMI down to 28 by the end of that year. Looking at our data, you can see he is doing well so far:

So it is now June, and Fred has dropped down to a BMI of 33.02. The Macro we are going to record is simply going to copy the BMI each month when he recalculates it, and add it to the graph data, to update the graph. Like I said, really simple. You may want to try replicate this to see if you can get the Macro to work. If you want to, the formula for the BMI is: =C6/(C5*C5)

I have the graph data set up on the same sheet, like this:

 So we are nearly ready. We just need to insert Fred's weight for July into cell C6. Lets say he is now weighing 110kgs, which gives him a BMI of 32.14.

Now, lets record. I'll show you how to start recording, and then talk you through each step. So go to the Developer tab on your menu ribbon, and click on Record Macro. You will get a box like this:

Choose a name for your Macro, but note that it won't accept spaces in names. You can also assign a shortcut key of your choice. I also use the letter in capitals as it is less likely to already be assigned a shortcut role.

If your Macro is specific to a particular workbook, then choose the option shown above. However, if it is something you would like to use in all your spreadsheets, store it in Personal Macro Workbooks, and it will be available whenever you open Excel.

Also note that Macro needs to be saved in a Macro Enabled Workbook, so make sure that is what your workbook is saved as.

Click Ok and remember that everything you do now is recorded. You will get a little square in the bottom left hand corner of your workbook which you need to press to stop recording the macro.

Then you just do the following:

Click on Cell E6, and press Ctrl C to copy
Click on Cell N3, and press Ctrl + down arrow (this should take you to June's BMI figure)
Click on the down arrow once more (to take you to July's space) and Paste Special - Values
Press Esc
Press Ctrl S to save.
Click on the little square at the bottom left hand side of your spreadsheet, or go to Developer, Stop recording, and you're done. Your table data is updated, your graph is updated. Life is good.

This looks easy, no? Well, hate to break it to you, but there is already an issue with this macro. If you go to your developer tab, click on Macro, select your Macro and click the Edit button, this screen will appear:

This is your VBA code. Cool, isn't it? See that line I have highlighted? That's the problem. Because when you select Cell N3, and then press Ctrl + Down Arrow, it takes you to the last cell with data in it. You then press down once more, and the Macro recording selects the absolute cell reference of N10. Which is fine for July, but in August, when you run the Macro, you will find it saves the BMI into the July cell. How do we fix this?

We change the code to say "Go to N3, then go to the last cell with data in it, then go one row down." How? Easy - instead of having the code as highlighted above, you replace that one line with :

ActiveCell.Offset(1,0).Select

This will ensure you always paste in the next empty cell in column N. The offset function is great for Relative referencing, as you can move around by choosing how many rows and columns to move by, instead of selecting a specific cell.

Whenever you make changes to your code, always remember to press the 'Reset' button. Its the little square highlighted below:

Then you can close out your VBA screen and you should be ready to run your Macro. How do you do this? Either by pressing Ctrl + whichever shortcut key you selected, or go to Developer-Macro-Run Macro. Your Macro will now add in the new data every month, and the graph will update automatically.

Another way to do this is to assign a button to a Macro. My newsletter this week will describe how to do this. Not subscribed yet? Do so now, here.

If you want to play around with Macros in your own time, I can recommend purchasing a textbook to help you through the minefield.My favourite author on all Excel related matters is John Walkenbach, but there are loads out there.

Enjoy, and hope I haven't scared you off Macros for life. They are great little timesavers, once you get over the set up!

Happy Spreadsheeting!

Natural Disasters - what is the cost? (and can we use a SUMIF to find out?)

Hello all. Today my hometown was flooded. Badly. I live up on a hill, so avoided the floods, but was without water and electricity for most of the day. However, a lot of people will have lost their possesions and their business stock but thankfully not their lives. My heart has been with them.

I am also a geek, so after getting over the irritation of being inconvenienced by Mother Nature,  I started wondering about the cost of natural disasters. I found this helpful map, courtesy of The Economist and Munich Re, on the top 10 most costly natural disasters:





In table format, the economic stats look like this (please note that if there are 2 disasters combined, I have just split the costs equally):

Scary stuff.  But lets try analyse it a bit. So what if you wanted to see which disaster type costs the most, in total and on average? You would do this:



I have added in the formulas for the first row (Earthquakes) so that you can see how I came to the figures. Now, before you start praying for a natural disaster to sweep me and my formulas away, let me try explain them in English.

What a SUMIF does is look for stuff in a column within a table of data that matches a criteria you specify, and when it does find those items, it sums up all the corresponding figures.

So for the earthquake line, to add up all the costs of earthquakes, you are saying =SUMIF(look in the Disaster column, for all the Earthquakes, now add up the corresponding costs).

COUNTIF works pretty much the same, except it just counts the number of times the specific criteria appears. So to count the number of times Earthquakes appear on the list, you say =COUNTIF(look in the disaster column, count how many times Earthquake appears).

Easy as pie. The average calculation then just flows from the two answers, and indicates that after Earthquakes, Tsunamis are not be trifled with in terms of economic damage.

Now.....what if you wanted to check the total costs just in Japan, pre and post 2000?

A SUMIFS  formula follows the same concept as a single SUMIF, you can just add more criteria. Also, the order changes in the formula. So to sum all the costs in Japan pre 2000, you say: =SUMIFS(these are the costs to add up, when anything in the Year column, is less than 2000, and anything in the country column, says Japan).

It really will make sense when you get the hang of talking to yourself when you're working....!

So that's your brain bender for this week. In this week's newsletter I will be discussing some other useful Count functions. Sign up here if you would like to learn more....

Happy spreadsheeting, and stay safe and dry!





Square pegs into round holes and the wonderful dropdown list

Meet Matt. Matt is a 13 month old toddler, and his mom has given him a shape toy. You know the one I mean; you have to fit the squares into the square-shaped holes, triangles into the triangle-shaped holes…. ? Well, Matt is exploring this toy, and while he might sometimes, by pure luck, get the right shape into the right hole, most of the time he will get it wrong. And what happens then? Either pure frustration, followed shortly by a tantrum, or he’ll get bored and look for another toy.

But why do we give children these toys? Not to torment them, but to teach them shapes, spatial awareness, colours…all sorts of useful things to help them through later life.

What has all this to do with Excel? Well, have you ever received a spreadsheet, and when you try type in your data into a specific cell, you get a message like this:

This is Excel’s version of acting like your mom. It is saying, slowly and clearly, “Try again, little one, the data you input just doesn’t fit in this cell”. Now, while it may make you have a small tantrum and want to throw your laptop against the wall, the person who created the spreadsheet did this for a reason. They have stipulated, quite clearly, that only certain types of data may be entered into that cell. How did they do that? Well, you would tell a 13 month old its “Magic”. For Excel users, the answer is “Data Validation”.

This little gem (or mind bender, depending) can be found on the Data tab of your menu. It looks like this:

As you can see, there are a number of options you can select. With each option, you will be asked for more details. So for example, if you select whole numbers, you then need to select whether the number needs to be greater than, equal to, etc. a specific number. So let’s use this list of children as an example. We are entering the names of children attending preschool, so we would not expect a child to be older than 6. We would select all the cells in the 'Age' column and apply the following settings:

By stipulating this, if you had to then type in 7, you would get an error message. So what happens if you got the same list of children, with their ages already filled in? You need to check there is no-one over 6, so instead of manually checking each age, you can select the whole cell range that contains ages, and apply the same data validation as you did above. Nothing will happen. But if you then select “Circle invalid data” as below:

…all your invalid data will be circled, awaiting your correction.

To prevent future tantrums, you can assist users of your spreadsheet by supplying an Input Message which will appear if the cell is selected. To use this option, fill in the details like so:

This will result in a little pop up box giving the user your message. Likewise, by filling in the details on the Error Alert tab, if someone does fill in the wrong number or text, you can help them by detailing what they need to do to get it right:

If you’re feeling sweet, you might actually lead them in the right direction!

One of the most useful of these data validation tools in the “list” option. This is how you create a dropdown list. If you have a number of items in your list, you can create the list elsewhere in your workbook, and then reference the list co-ordinates in the source box, like so:

However, if your dropdown list is short, maybe it is only Boy or Girl, you can type them directly into the source box, separated by a comma. You would then have a drop down list in your column as shown below:

2 more points:

1)      You may have noticed there is a “Custom” option. This applies to formulas and warrants a blog all on its own. However, I will be giving a taster in my newsletter. Why not sign up?

2)      Data validation rules will be deleted if you copy or drag another cell into the cell with the data validation. So be careful! (Now I sound like your mom….hmmm...sorry about that!)

Happy Spreadsheeting!

Who's afraid of the big bad vlookup?

Little Red Riding Hood is lost. And it's not in a dark, scary wood...oh no, it's in the lot of a Hollywood Studio. You see, what with all the rehashing of classic fairytales at the moment, the casting directors are getting confused and sending the actors to the wrong studios. What they need is a good vlookup formula to get themselves on track (yes, seriously)......

So imagine these were there current projects:

What this table shows, besides the fact that handsome princes are in demand and fairies and stepmothers have got a bad name, is that there is a fair amount of opportunity to get confused with all the various details.

If each casting director were sent a lovely little lookup tool as shown below, they could select their fairytale from the dropdown list, and the answers would automatically flow into place, along with the correct studio number.

Now before you fall into a 100 year sleep, let me explain VLOOKUPs as simply as I can. The official syntax of a vlookup is:

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup).
Yup. Hardly surprising it sends most people running for their fairy godmothers. Instead of fleeing, if you are using a VLOOKUP formula, try saying this in your head as you type it out. It may help you make sense of the formula:

=VLOOKUP(look for this, in here, bring back stuff from that column, false). So much easier than the official set of syntax, no?

Here is what the formula means, in plain english:

lookup_value - this is the cell reference of the item you want to search for in the data table. So for our example above, the lookup_value is the Fairytale name.

table_array - this is the table of data that you are using to search for all your answers.
NOTE: for vlookups to work, the first column in the table of data needs to be the column containing your lookup_values. In our example, that means the 'Fairytale' column must come first.

col_index_number - this will depend on what data you want to bring back. In our example, to bring back the Leading Lady, you insert a 2, as this info is contained in the 2nd column of data if you start counting columns (left to right) from the column that contains your lookup_values.  For Good Guys, use 3, for Bad Guys, use 4 etc.

range_lookup - This is always either TRUE, FALSE or you can leave it out, which is the same as typing in TRUE. To get an exact match on your lookup_value, type in FALSE. If it can't find an exact replica of the item you are searching for, you will get an #N/A. If you type in TRUE, it will look for the nearest match and bring back that data. This has its uses (see below) but in our example, you would always use FALSE. In fact, whenever you want 'exact match or nothing', use FALSE.

Some points on VLOOKUPS:
1) If you have duplicates in your first column, the vlookup will always bring back the data from the first instance. So check for duplicates if you're not getting the answer you expect.
2) If you are going to be copying the formula into a number of cells, make sure your table data is 'locked' into the formula. So select your table co-ordinates and press F4 to get the little $ signs.
3) Spaces matter in vlookups. If you download or import data from a reporting system, it is quite likely there may be a space at the end of your data. This will prevent your vlookup from working. Delete the space, et voila.
4) If you are not quite comfortable typing in the formula freehand, use the formula function box to help you the first couple of times. After that, you will probably find it much easier just to type it yourself.

So, back to the issue of TRUE or FALSE. Using TRUE is really handy if you want to look up a number that falls within a range of numbers. So if you were grading papers and wanted to give students an A-F grading based on their % marks, this is a handy tool to use.

Similarly, if we wanted to give our characters a 'goodness' rating, we could do the following (I've added the formula in at the bottom again for explanation):

This formula works by first checking for an exact match. If it can't find it, it will look for the next LOWEST number. So your table always needs to be in ascending order. In our example, Mother Gothel has not yet reformed her ways enough to even be classified as Misguided (40%), so she goes straight into the Evil & Twisted basket.

So, that is your basic VLOOKUP. These formulas are normally used for extracting data from one table into another, but can be used for just about anything. I've kept it simple for ease of explanation, but you can add in all sorts of other functions to make these formulas truly perform magic.

In our newsletter this week, I will be explaining how an HLOOKUP works...why not subscribe?

Happy Spreadsheeting!


--

How do I sum thee? Let me count the ways.....

Do you know what I really love about Excel? The fact that everyone uses it differently. There are so many ways to reach the same answer, and people will use the techniques that they have been taught, or find the easiest.

Take the simple task of adding totals and subtotals to data, for example. I had a debate with my better half the other day about this very subject. He went for  the subtotal function. I went for pivots. It wasn't too heated (because hopefully we aren't that sad yet!), but it made me think about all the ways you could do it.

So here are some examples of how to get sub totals and totals for data with different categories (and bear in mind these are not all of the ways to do it... but we don't have all day!). Please note too that I am going to ignore the most simple and obvious choice of the sum function. Pure snobbery, but we have to have standards...

Our test data for today is riveting...unit sales on widgets, sprockets and gadgets:



Subtotal Formula Function
If we side with my better half, we can get subtotals for each product by doing the following:

 - Select all the data, and sort it by Product.
 - Insert lines between each product type
 - Add the sales for each product grouping by using the subtotal formula function as follows:

Things to note on this method:
 - When typing the Subtotal formula, note that the 9 (or you can use 109) refers to 'adding'. If you type in" =subtotal(" into your spreadsheet, you will notice a little drop down box appears giving you all sorts of lovely options, like average, max etc. The number you type in directly after the open brackets will determine what magic your formula will perfom.

 - The formula in the total line at the bottom will ignore all the subtotals above it. Handy.

Subtotals with grouping

An easier way to do this is click on the data, go to your data tab, and select the Subtotal button. You can then select what type of formula function you want, for which columns and hey presto, you have the same as above. You also get little brackets along the left side of your sheet that you can open and close for ease of viewing the totals, like below:

SUMIF Function

Has anyone seen the issue with this method yet? Well, one of the issues anyway...What happens if we want totals by colour too? You have to rearrange everything. Or, you can do this:

By using the SUMIF function, you can create sub totals by product or by colour, without having to touch the data. I've shown the formula above for clarity, but what it is basically saying is "Look in column B for any cells that match "Widget". Where they do, add up all the corresponding figures in column D". You can then drag this formula across to get the totals for all your products and colours.

The plus side of this is that you don't have to rearrange your data to get totals for different categories, and it is easier to add more data.

Pivot Tables

In my mind, this is still the cleanest option. By using a pivot table to get subtotals and totals, you can cut and slice your data however you choose. A basic pivot of the data would look like this:

By using the Pivot Table Field List, you can then choose whatever options you like for your pivot table by mixing it up.

For example, if you wanted these totalled by colour, simply swop the positions of Product and Colour in the Row Labels box.

This is a basic Pivot Table, because they deserve a whole blog on their own. But if you would like to see how I would mix this data up even more in a pivot, why not subscribe to our newsletter?

At the end of the day, you may say To-may-to and I may say To-mah-to, but we'll both be happy with our way of doing things. I just hope this has shown you a couple of other ways to try add it all up....

Thanks for reading, and happy spreadsheeting!