Decision Making with Excel

This page is aimed at later versions of Excel and in this example I have used Excel for mac²⁰¹¹

Imagine you have a decision to take but the number of variables on which to take the decision makes the process relatively complex. In this post, then, I am going to show you how I used an Excel Table’s filtering capabilities to help me take a decision.

The decision I want to take is to decide which one of a choice of many possible houses should I build? I have created a list of 51 house designs and summarised the key features of each house, such as

• on reviewing the photographs and descriptions of the house, did I initially like it and entered y for yes in the favourite column?
• number of bedrooms
• number of shower rooms
• whether the master bedroom is separate from or on the same floor as at least one other bedroom
• overall floor area of the house
• and so on

Here is a screen shot of part of the Excel Table I have created and that is followed by the results of my initial interrogation of that table.

Imagine that I am looking to satisfy these conditions:

• I must like the house: it is a favourite
• I want a minimum of three bedrooms and a minimum of two shower rooms
• The master bedroom should not be isolated from all other bedrooms and it should have an en suite shower room

To see what this reveals for me, start with the column headed Favourite and select only those marked Y … click on the down arrow in the header row of that column to see the options … immediately you will see that the number of houses to choose from falls from 51 to 9:

Now we should select and filter the number of bedrooms and shower rooms … minimum 3 and 2 respectively …

I have shown the master bedroom as alone with the value of 1 and not alone with the value of 0; and it has an en suite shower room if the value in that column is 1 … in that column, two houses score 0.5 for en suite master bedroom because whilst it is en suite it is shared with another bedroom or the rest of the house. Select only value 1 for truly en suite, therefore.

We have narrowed down our eligible properties to five now so let’s take another step: cost … in fact Adjusted Cost. I found the cost of building from four years ago then I found the building materials index from the Government and adjusted the old building costs accordingly! Let’s say we don’t want to pay more than 2.5 for our house … filter that column and see what is left for us to choose!

That leaves us with three properties but I notice that design 22 is wide enough at 15 metres but it is not deep enough at only 12.5 metres so I will filter that out now.

Two houses remain. What’s left to filter out? Well, do I really need four shower rooms? No, so filter that out now … House Design 7 is my choice of house to build according to this Excel Table and the filtering of it!

Do this yourself: create a table like this one that relates to a decision you need to take. Take a close look at the desired features you think  you need. Filter out or filter in as you see it and see what’s left.

Clearly, this is not the only way to resolve these problems but using an Excel Table where you have already set up the logic and syntax of the table, is a very efficient way of working. choosing one house to build from a list as short as just 51 alternatives is, believe me(!), an onerous task.