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!

How to make Ireland's rugby ranking look good....

Well, pretty at least. When viewed on a spreadsheet. What am I on about, you may wonder. Bear with me...

Have you ever seen those photographs of celebrities where the poor girl has dared to leave the house without a bucket of makeup on? The horrors! The tabloids point out that she looks pretty normal without all the airbrushing and uplighting, and we all think, "We could walk right past her on the street and not notice".

The same goes for spreadsheets. They like a little lipstick and airbrushing. It makes them stand out. Don't believe me? Compare the two sets of exact same data below, showing the IRB top 8 rankings for the last 3 years, and the movements year on year:

Example A:

Example B:

Now, feel free to disagree with me, but I find example B easier to read (especially when its a 1000 lines of data over 30 columns you're analysing). You can immediately see who has moved up, who has moved down, and who has stayed consistently at the top (damn those Kiwis).

It took me all of about one and a half minutes to re-format the boring numbers  in Example A to the clear and concise numbers in Example B. Here is how:

1) Lose the gridlines: See my earlier blog on how to do this...

2) Choose your theme (this only applies to Excel 2007 and 2010): A theme basically sets a colour chart and font for your workbook. It influences the colours you can pick for cells, graphs, pivot tables etc., so choose wisely. If you haven't done this before, here is how:

Go to Page Layout - Themes. The drop down arrow will show you all the theme choices with their basic colour range. Select your particular fancy, and you're set. Note that next to the 'Themes' box, you also have options to change only the colour, font or effects. Play away to your heart's content until you're happy.

Note that if you decide halfway through that you would prefer a different colour chart, change your theme. Everything will follow suit, but note your columns and rows may change size too, so check your print preview before printing.

Note, also, that you can skip this step. Your worksheet will show the standard Office colours.

3) Highlight your headings: Select cell B2, and using your menu options on your Home menu tab, change your background colour, text colour and make the text bold.

4) Use your format painter: For anyone who hasn't discovered this little gem, be prepared for a time saver of note. Go to your home menu tab and find this:

Point to it, right click, and add it to your your Quick Access toolbar, because you should be using this tool all the time. What this tiny little brush does is copy all your formatting in your highlighted cell and will apply this formatting to all other cells you select afterwards.

So you can use this to format all your headings in Row 4 in one go, by simply clicking on Cell B2, clicking on the format painter, and then selecting Cells B4:G4.

5) Center your data:  by using the alignment buttons on your Home menu tab.

6) Center and merge:  your main heading by selecting Cells B2:G2, then clicking the Merge & Center button in the alignment segment on the Home menu tab.

7) Use Conditional Formatting: to show the positives and negatives in the YOY columns. Select cells D5:D12. Still on the Home menu tab, drop down on the arrow on Conditional Formatting and select Highlight Cells - Greater than

Type in 0 in the box provided, and select your colour choice. I went for Green for anything that had a movement greater than 0. Then repeat for Less Than.
With your cells still selected, use the format painter to apply the same conditional formatting to cells F5:F12.

8) Apply borders: Note I left this till last. You really want to be happy that you have finished adding to your data before you apply borders, otherwise if you add anything later, you can spend a lot of wasted time redoing them.

I have chosen to add blue borders, as opposed to the standard black ones. To do this, select the cells you want a border around, and then go to
Home - Borders - More Borders.... Select your border style and colour FIRST, and then select where you want your border applied.

And you're done. The neat formatting might even distract you from the fact that Ireland has dropped four places in one year. Or not.

These are VERY simple formatting techniques. It can get a whole lot more complicated, but it very much depends on your data and what you will use it for.

For some helpful tips on other formatting techniques I have found handy over the years, sign up to our newsletter.

Happy Spreadsheeting!

The most effective "three little words".......

Meet Sam. Sam has a dilemma. He has met the woman of his dreams - lets call her Suzie. They have had a delightful couple of years together, and Sam can picture himself spending the rest of his life with her. He wants to get down on one knee, declare his undying love, and beg for her hand in marriage (yes, soppy for an Excel geek, I know).


So what's the dilemma? Well, Suzie is already turning into a bridezilla. She has been anticipating this day, and presents him with a list of six rings, along with their basic characteristics. When Sam asks her, in some trepidation, what they cost, she airily replies, "about €5000". Sam is a bit of an Excel fan himself so does up this little spreadsheet, to help him budget:

Ah Sam, bless him. He hasn't done his research. He doesn't know about the 4Cs of diamond buying (and for ease in this example, we'll only concentrate on one). He doesn't realise that bigger rocks mean more money, and that platinum is quite a pricey commodity.

So how can we help him? We use the three little words that really mean something...IF, AND and OR.

Now, a couple of caveats before we go further....
a) I am not a jeweller and am sadly not in the habit of buying diamond rings all that often, so these prices are completely made up, and are not particularly logical. Its all for the sake of example....
b) There are other ways of working this problem out - vlookups, modelling tools etc. I am going to do the following examples purely to illustrate how to use IF, AND and OR in formulas.

Lets assume that the base price of €5k applies to a 0.5 carat diamond on a gold band. Sam gets told by his friendly jeweller that all platinum rings incur a 15% markup to cover the cost of the metal. Easy way for Sam to update his sheet? An IF statement.

An IF statement basically says "Is this statement true? If yes - do A, if not - do B". So our formula checks to see whether the metal in column C is platinum, and if it is, it takes the base price and adds the mark up of 15%. If it isn't platinum, the formula just picks up the base price as is.


So Sam thinks he is sorted, but alas no. Our friendly jeweller tells him that a 1 carat diamond carries a hefty markup of 60%, but for this price, they will exclude the additional cost of a platinum band. What now? Well, we use 2 IF statements in one...like so:

This formula uses the first IF statement to check whether the diamond is 1 carat. If it is, it adds on 60%. If the diamond is not 1 carat, then instead of going to the base price, it then checks if the metal is platinum, and adds 15%. This is the 2nd IF statement.

Note: - It is ESSENTIAL to get the logic right in these formulas. If you had checked the metal type first, and you had a platinum ring with a 1 carat diamond, your formula would only show a 15% markup.

You can nest an IF statement quite a few times in one formula, but beware. It gets very complicated and if you find you have 10 or 15 IF statements in one formula, you might want to step back and see if there is an easier way.

Anyway, lets get back to Sam. He heads to another jeweller who has a different pricing system. This jeweller tells Sam that any diamond equal or greater to 0.7 carats, on a band of platinum, has a markup of 30%.

Here we have just nested an AND statement into an IF statement. So instead of one statement needing to be true, BOTH statements need to be true - the diamond must be greater or equal to 0.7, AND the band must be platinum - for us to do step A, which is add on 30%. If BOTH statements are not true, then the formula will revert to the base price.

Now, clearly this pricing is not quite logical. So lets say that if EITHER the band is platinum, or the diamond is greater than or equal 0.7 carats, it gets a 30% markup. We can now use a nested OR statement:

This is the same as the nested AND statement, except in an OR statement, only ONE of the criteria needs to be true for the formula to implement step A.

So there you have it, my favourite 3 little words. Well,when it comes to Excel anyway. Obviously these formulas can get much more complicated, as you can nest away to your hearts content and make things as complicated as you would like. I just wanted to try give you a basic idea of the logic behind these simple, but oh so useful, statements.

For some more ideas on how to use these statements, why not sign up to my newsletter? Just click on this link and fill in the details...

http://www.excelangels.ie/faqs_and_weekly_tips_8.html

Happy Spreadsheeting!

A lifeboat for Excel's stormy seas....

"Its not the boat, its the skipper". This harsh advice was often directed at me by my father when I came off the water. I'd be blaming my poor performance on the sail, mast, rudder, wind, anything but me. And if I'm honest, it was all me. Sailing was truly not my forte. I was born to be a landlubber.

I read a blog about Excel today that reminded me of those happy times. It was posing the very valid question of why Excel always gets the blame when reported results turn out to be incorrect. I read this and thought of my dear father. I would willingly bet my house that almost all of the errors that are blamed on Excel, are due to the Excel user.

It is way too easy to make an error in Excel, due to being rushed (who hasn't been under deadline pressure?), not sense checking the results, or - the scariest - not fully understanding what Excel does, and therefore not knowing that the formula you think is perfect, is actually picking up the wrong data.

In an attempt to throw out a lifebelt to all those in difficulty, I have listed below a couple of the most common (and easy) errors to make, and how to prevent them. (And yes, I know these well, because I have fallen into their evil little traps before...)

1) Circular references

You may be thinking, hang on....Excel tells you when you have a circular reference. You're right, it does.When you create a formula that contains a circular reference, an error message pops up that helpfully lets you press ok and allow the circular reference. You then save and close the spreadsheet. When you next open it, it doesn't bother telling you again that you have a circular reference.

Therefore, you open a spreadsheet that maybe someone has sent you, you assume all is ok, and you happily use the results given. You would be wrong. And you may just end up blaming Excel. Before you do, check the bottom left hand corner of your spreadsheet. If there is a circular reference, it will tell you exactly what cell contains the wrong formula. If the error is in a different sheet within your workbook, it simply states 'Circular References'. Go through each sheet until you find the pesky blighter. Ignore it at your peril.

2) Is Excel set to Manual Calculation?

Again, you may be thinking 'Doh'. But really, it happens. You're working with huge sets of data, way too many formulas hanging off them to be practical, and everytime you press enter, your spreadsheet churns through all those figures and formulas to recalculate. So ten minutes and a cup of coffee later, you decide to make your life easier and you switch on 'Manual Calculation'.

(In case you're wondering how, go to Formulas - Calculation Options, and the box below will pop up...Make your choice wisely....)

You enter all your data with ease, you print your dashboard that picks up totals from each of your spreadsheets, and wa'hey....your results are all wrong. Why? Because Manual Calculation is still on.

To prevent this catastrophe while using Manual Calculation, do both of these:

• Press F9 on a regular basis. This recalculates all your formulas immediately.
• Check that handy little bottom left hand corner on your spreadsheet before you print or send anything. If it says, "Calculate", heed the warning and press F9.

And remember to switch back to Automatic when you're done.

3) Are your sums right?

Not such a stupid question. Here are 3 scenarios where your autosums might not be adding up:

a) You have a filter on. This can sometimes throw out your sum function, depending on how your spreadsheet is set up and what you are trying to sum. How do you know whether you have a filter on? Simple - if the row numbers on the left are blue, you have a filter applied. To quickly remove a filter that is applied, and show all your data, press ALT D,F,S.

b) You think you have dragged down to include new rows in your sum, but instead of dragging, you've just moved the range. A picture will explain this better:

If you wanted to include row 7 in your sum, you can point your mouse at the bottom right hand corner of cell B6 and a little double headed arrow will show up. Drag down to row 7 and voila, your sum now includes everything.

The error comes in when you drag down when the four headed arrow is showing, not the double headed arrow. This results in you just dragging down the range. In this case, your sum would be SUM (B4:B7). Easy mistake to make, not so easy to spot when you have thousands of rows to sum.

So how to spot it? Excel tries to help again (see, it is NOT the enemy!). A little green triangle appears in the top right hand corner of the cell with your formula. Click on it, and it will tell you that your "Formula Omits Adjacent Cells". Go find those cell numbers and include them.

c) You simply have left a cell out. This is again, easy enough to do, especially when you are trying to add only certain cells, so you add each one individually. The easiest way to check that you have included every cell you should have, is to click on the cell with your formula, and then click once in the formula bar. All the cells you have chosen will be highlighted by different colour borders, which makes it easy to identify what cells, if any, you have forgotten. See below for an example.

If we were just trying to add sales for the dinghies only, by using this check, we would immediately see that we have left out the sales for Dinghy B (cell B6).

As an aside, this trick is also very useful when trying to make sense of long, complicated formulas. The colours help track what you have done, and Excel even provides different coloured brackets for embedded formulas to make your life even easier.

So these are all fairly basic, but oh so easy, errors to make. And while I recognise that Excel is not perfect, when there are errors in reports or results, it is normally the fault of the skipper!

For some more handy hints on errors, why not sign up for our weekly newsletter at http://www.excelangels.ie/faqs_and_weekly_tips_8.html

Or check out our training program to avoid your own rocky seas at www.excelangels.ie

Hope your spreadsheet seas are calm ones this week!