I am preparing for a course that I will be running next week and since the major topic is Debtor Management, I felt it would be good to develop an Excel file to prepare an ageing schedule.
Once I had started I decided I could prepare a variety of solutions:
- using functions and formulae
- using conditional formatting
- using a Pivot Table
- using Excel Tables
Here are some screenshots that help to illustrate what I have done and after the screenshots, I show you some of the formulae I used in the development of this file.
In any case, this workbook does two things:
it allows pupils to consider a real ageing schedule albeit with a simplified example and to change the payment boundaries: for example, invoices owing between 1 and 30 days then 31 and 60 days … you can change it to 1 – 25 days then 26 – 50 days … and any range of days you like. This will help you to explore the credit control strategy and so on. It leads in to cash forecasting and budgeting too.
To determine whether we are dealing with an entry between 1 and 30 days, 31 and 60 days … =IF(AND(TODAY()-$B14>K$14,TODAY()-$B14<=L$14),$D14,”") … this gives the answer in values
to show the same results but in terms of days …
=IF(AND(TODAY()-$B14>K$14,TODAY()-$B14<=L$14),TODAY()-$B14,”")
Showing the status of a debtor’s balance: overdue or current …
=IF(TODAY()>C14,”Overdue”,”Current”)
Similarly, finding Overdue and Current allowing for the payment of an account …
=IF(D14>0,”",IF(TODAY()>C14,”Overdue”,”Current”))
Then there are the functionalities of Conditional Formatting and Excel Tables
The Pivot table, which I don’t show in screenshot form above even includes the suppression of zeroes.
An apparently simple problem but with some interesting programming to do.
Duncan Williamson
Creating a Hyperlink in Excel
16/08/2011
There are times when you might want to make an entry in an Excel file, range or cell that not only links to another file, range or cell but actually goes to that file, range or cell at the click of a button. Just as on a web page, Excel allows us to create a hyperlink to somewhere else on your computer or network or even on the internet. Setting up and editing a hyperlink is very straightforward as this example will show.
This morning someone asked me to show him how to set up hyperlinks in an Excel file that confirms if and when an invoice had been paid. Now, what you are about to see is not foolproof or even the best way to manage this situation but it is what the client wants to do: it’s how they manage their small project invoices.
In their Excel file this client has a table that records for each invoice such details as:
- Date
- Name of supplier
- Address of supplier
- Invoice number
- Amount
- Paid or not
Actually, it doesn’t matter what is in their file because all we are interested in is the final column, paid or not.
The brief, then, was to get Excel to point to a PDF copy of an invoice that has been paid and that will sit in the same folder as the Excel file itself.
Every month, the ZIP file will be sent off to a supervisor/controller who is monitoring this project and its payments.
To set up the hyperlink: firstly, ensure that the PDF invoice files are in the folder they are meant to be in.
- in the paid or not paid column type the word yes or paid or cheque issued … whatever you want … keep it simple and I will enter the word yes for all invoices that have been paid.
- good spreadsheet practice says that you should enter something in the unpaid cells just to show that you haven’t forgotten them. Leaving these unpaid cells blank is not good!
- select insert then click Hyperlink icon
- at this stage there are four choices:
- link to an existing file
- link to a place in this document
- create a new document
- link to an email address
- for this example, we are going to link to an existing file so click to choose the file to link to: the invoice file relevant to the current cell … this is in the folder you have created
- check that the Text to Display says “yes” … you can edit that at any time by coming back here at any time in case you forgot to type yes or even made a mistake!
- when you are happy with what you have done
- click OK
Your hyperlink is ready now and you can test it: to test it just click on the displayed text in the cell/link you just created and it should open the file or worksheet that you have linked it to.
Duncan Williamson
Combinations with my Breakfast!
12/08/2011
As one does, I went into my kitchen this morning and on seeing five different cereal packets in my cupboard, suddenly wondered, how many different breakfasts could I make out of those five cereal types?
For example, if I only had one cereal a day, then I could have five different breakfasts:
Monday = Muesli
Tuesday = Bran Flakes
Wednesday = Weetabix
Thursday = Malted Shreddies
Friday = All Bran
Suppose now, though, that I mix two cereals a day: for example, Muesli and Bran Flakes … how many different breakfasts can I have now? That is, in how many ways can I combine the five different cereals, two at a time?
Make a list: and prove that the answer is that you can have ten different breakfasts from a choice of five cereals taken two at a time.
Suppose now that I make a mixture of three cereals a day from the five cereals in my cupboard: how many combinations of breakfast are available to me now?
Check that you agree that the answer has to be ten again.
Choosing four from five cereals gives me a combination of five possible breakfasts and choosing five from five cereals gives me a combination of one possible breakfast.
That’s taken a long time hasn’t it? A lot of brain work and making of lists and tables. To see a more detailed background to this topic, take a look at my web pages on combinations (and the sister idea of permutations) here:
http://www.duncanwil.co.uk/permcom.html
http://www.duncanwil.co.uk/com.html
In that first page you will learn the difference between permutations and combinations: in simple terms, permutations count ALL possible variations whereas combinations exclude equivalent outcomes.
That is, permutations would count as three different breakfasts
Muesli + All Bran + Weetabix
Weetabix + Muesli + All Bran
All Bran + Weetabix + Muesli
whereas combinations say they are the same as each other … mathematically speaking.
Excel Functions: combinations and permutations
Let’s cut this page short now and introduce two Excel functions that save us from having to create these possibly lengthy analyses to find out how many possible breakfasts I might create from my five cereals:
=COMBIN(n,r) that is, find the combinations of r breakfasts from n cereals
=COMBIN(5,3) = 10 = I can create ten different breakfasts by combining three cereals from the five cereals available
=PERMUT(n,r) that is find the permutations of r breakfasts from n cereals
=PERMUT(5,3) = 60 = I can create 60 different breakfasts by perming three cereals from the five cereals available
The differences between the number of combinations and the number of permutations can be very large and the table below shows a table to compares the combinations and permutations for this cereal and breakfast case study.
| Summary | ||
| n | 5 | |
| r | Combinations | Permutations |
| 1 | 5 | 5 |
| 2 | 10 | 20 |
| 3 | 10 | 60 |
| 4 | 5 | 120 |
| 5 | 1 | 120 |
There you are: that’s what I thought about as I opened my cupboard to make my breakfast this morning.
Why not build the above table for yourself using =COMBIN(n,r) and =PERMUT(n.r)\; make n = 5 and r range from 1 to n; then change n to 10, say; and make r range from, say, 5 to 9 … as you wish.
Duncan Williamson
Recreating Charts: with analysis
22/03/2011
You might find that you read a newspaper or magazine article that contains a chart that you think you’d like to use in some way. One problem is that the chart is in the form of an image and the data on which it’s based is not available. It happens to me a few times a year.
This post then is concerned with turning the image of a chart into a table of data and then back into the chart … plus, plus. Read on to read a case study I have just prepared!
Case Study: recreating charts: with analysis
The purpose of this case study is for readers to extract and analyse data from a bar chart and data table. The case can be used in a wide variety of settings from secondary/high school level to undergraduate and professional examination level.
Having required the reader to recreate a chart from a published source, they are then required to comment on the new charts they create. In addition they are then required to discuss the possibility of an outlier being found in the data and comment on how such an outlier might be dealt with.
Finally, the reader is required to consider the situation in which the outlier is left in the data set and comment on the data set without adjustment.
Required
1 From the following chart, taken from The Economist, set up a spreadsheet file to achieve the following
a) Extract the data from the bars in the chart
b) Recreate the chart from The Economist in your file
c) Create a further chart to illustrate the percentage of domestic consumption data
2 Comment on the charts you have prepared in task 1
3 a) Prepare a third chart with TeraWatt Hours on the X axis and % of Domestic data on the Y axis and comment on your new chart.
b) In your new chart there is an outlier: suggest which data point might be an outlier and recommend what can be done with it from a statistical point of view
c) Comment further on your second charter further once you have eliminated the outlier data point
d) Suggest an alternative approach to dealing with this outlier
© Duncan Williamson
March 2011
There is a solution to the case study for anyone who is interested and it is available for just GBP10 or US$16: the solution is fully worked and it includes a 14 minute video that clearly sets out the two main spreadsheeting techniques needed for the case. Send me an email to sales@excelmaster.co.uk with a brief note to tell me you want to buy the solution and I will send you full instructions.
Duncan Williamson
In this blog I am going to introduce two things I am ashamed to say I have only just learned. It just goes to show that even after all these years of using and learning Excel spreadsheeting, new things can still appear.
I am going to talk about
- · =RAND*(b-a)+a
- · =RAND() and the F9 key
=RAND*(b-a)+a
The formula =RAND*(b-a)+a is used instead of =RANDBETWEEN(…). So if you want to generate random numbers from, say, 7 and 11 then a = 11 and b = 7. That is, a>b.
The following screenshot shows you the effect. In this screenshot I have generated the random numbers in their own cells using =RAND() and then used, for example, =A1*(b-a)+a to finish it off:
Press the F9 key to refresh this volatile function and see that it does give you randbetween using rand. Please note, the answers are NOT integers, use =RANDBETWEEN(…) for that.
That’s is, but here is a screenshot of a chart I prepared so that by pressing the F9 you can see how random your chosen numbers are. In my case I created a table with two sets of 30 random numbers, X and Y.
=RAND() and the F9 key
I really am surprised that I did not know that this utility existed. Imagine you want a random number that is not volatile … you just want to generate such a number and don’t need it to refresh every time you hit F9 or Enter.
Put your cursor in the cell where you want a random number to appear. Then in the FORMULA BAR and NOT in the cell, type =RAND() and then press the F9 key. That’s it and in your chosen cell you will have a random number and not =RAND().
This also works with =RAND()*n where ‘n’ is any number
Duncan Williamson
I am often asked the question of whether it is possible to prepare an income statement and a balance sheet in Excel. I always reply that of course it is simple to do that providing you know the basic layout of those statements.
This set of notes illustrates that all you really need to prepare an income statement and a balance sheet using Excel are:
· The data: a list of revenues, expenses, assets, liabilities and capital are all you need. In these notes, I will be dealing with the income statement and balance sheets separately
· A Pivot Table: well, in this set of notes, two Pivot Tables but the homework at the end of the notes is for you to combine everything into one list and one Pivot Table
I am assuming that you already know the basics of
- · Financial statements
- · Preparing and using a Pivot Table
This is a lengthy note so I have put it into a PDF file for you to download and work through at your own pace.
What you are aiming at here is to prepare these Pivot Tables from lists of data:
The Excel spreadsheet i used to create the above was corrupted beyond repair by Excel 2010 after I gave it the massively onerous task of adding headers to each worksheet. After all, after only 25 years of development, we’ve got to give Gates and his tribe a fair crack of the whip and admit that adding a header is one of the more advanced functions built into Microsoft Office’s 2010 suite.
Duncan Williamson
Resource Histogram
03/01/2011
I have to confess that I had never heard the phrase Resource Histogram before last night. A colleague asked me if I could show him how to prepare one of those histograms in Excel. I asked him to show me an example and I would do what I could. Well, here is the chart I was given and of course, that is NOT a histogram at all but it is what I was given and how to prepare it?:
What I did was to create a table of data from the chart I was given then ask Excel to create a Stacked Column Chart. That’s it apart from adding chart titles and an axis label and so on. Here is the table of data I put together and on which the above Resource Histogram is based.
| Task | Jan | Feb | Mar | Apr | May | Jun |
| Managers | 1 | 1 | 1 | 1 | 1 | 1 |
| Business Analysts | 2 | 2 | 2 | 1 | 1 | 1 |
| Programmers | 1 | 1 | 4 | 4 | 4 | 1 |
| Technical Writers | 0 | 0 | 0 | 1 | 1 | 1 |
To Draw the Resource Histogram
- Select the whole table
- Insert
- Charts
- Column
- 2-D Stacked Column
That’s really all there is to it!
Duncan Williamson
Gantt Charts in Excel
03/01/2011
A Gantt Chart looks like this:
And lots of people use them and/or need to use them. The question is, how on earth do you prepare them in Excel. After all there isn’t a Gantt chart option in there. It’s true, there is no Gantt chart option in Excel. It is possible to draw a Gantt chart in Excel though because that’s where that chart above came from!
Here are the data on which the above Gantt chart is based:
| task | start date | duration | end date |
| planning meeting | 29/12/2010 | 1 | 29/12/2010 |
| develop questionnaire | 30/12/2010 | 11 | 09/01/2011 |
| print and mail questionnaire | 13/01/2011 | 9 | 21/01/2011 |
| receive responses | 16/01/2011 | 15 | 30/01/2011 |
| data entry | 16/01/2011 | 18 | 02/02/2011 |
| data analysis | 03/02/2011 | 4 | 06/02/2011 |
| write report | 09/02/2011 | 12 | 20/02/2011 |
| distribute draft report | 23/02/2011 | 1 | 23/02/2011 |
| solicit comments | 24/02/2011 | 4 | 27/02/2011 |
| finalise report | 02/03/2011 | 5 | 06/03/2011 |
| distribute to board | 09/03/2011 | 1 | 09/03/2011 |
| board meeting | 17/03/2011 | 1 | 17/03/2011 |
A series of tasks with a start date, an end date and a duration: typical data for a Gantt chart. Please note, the end date column and data are not needed to draw the Gantt chart, they are there for information and confirmation
Please copy and paste that table into a worksheet with the heading task in cell A5. Now work your way down the following instructions that relate to Excel 2007 (and 2010):
How to Draw a Gantt Chart
1 Enter the data as shown in Table 1. The formula in cell D6 (end date), which was copied to the rows below it, is =B6+C6-1 the end date column is NOT used in the chart but is for information to support it
2 To create a chart select the rangeA6:C17 then click Insert and create a stacked bar chart … use the second subtype, which is labelled Stacked Bar.
3 Notice that Excel incorrectly uses the first two columns as the Category axis labels.
4 Right click the chart and click on Select Data to open the chart wizard. Now, set the chart’s series to the following:
- Series 1: B6:B17
- Series 2: C6:C17
- Category (x) axis labels: A6:A17
Click OK to leave the chart wizard to create an embedded chart.
5 Delete the legend
6 Create or amend the title and add the horizontal axis label … this is no real need for the vertical axis label but feel free to add Task if you wish.
7 Adjust the horizontal axis Minimum and Maximum scale values to correspond to the earliest and latest dates in the data (note that you can enter a date into the Minimum or Maximum edit box). You might also want to change the date format for the axis labels.
8 Right click the vertical axis and select the Number sub menu: select Format Axis dialog box for the vertical axis. In the Axis options select the option labelled Categories in reverse order and also select the option labelled Horizontal axis crosses at maximum category.
9 Right click the first data series and select Format Data Series. In the Fill section, set fill to No fill and Border Colour to no line. This makes the first data series invisible and is the key to this chart.
10 Apply other formatting, as desired.
That’s it! Read this information once, twice, as many times as you need. Then enter your own data, adjust the formulae and change the data series in the chart, as you need.
Duncan Williamson
Turning a Chart into a Graphic
26/09/2010
In the old days when spreadsheets were driven by steam we would happily copy and paste charts from Excel into Word and PowerPoint and worry sometimes that they didn’t look that good. The worry was partly that MS Office is not always as smart as it pretends when taking one element of a spreadsheet or page to another MS application.
So you prepare your chart and drop it into a Word file and resize it … bad idea! Everything can change: the bars or lines change, the font changes size and the thing can get very messy.
More than that, there are times when you don’t want that oaf in the next office who thinks he’s an Office genius to "improve" your chart in any way. After all, your chart complies with corporate communications policies and so on. The oaf thinks that pink and orange together with blue and green are fantastic colour combinations!
So for at least two reasons you might want to turn your chart into a graphic that cannot easily be changed and that will look good in an application other than Excel.
Old Fashioned Way
I just read a blog that recommends a pre Excel 2003 solution even though there is a brilliant solution in Excel 2003 … 2007 … 2010: the camera tool!
The blog says: create your chart then copy it and paste it into a graphics package and save it, making any changes or enhancements you like. Well, OK, but why would you want to start opening yet another application when Excel can do everything for you with the Camera Tool?
The Camera Tool
Firstly, put the camera tool on the QAT (the Quick Access Toolbar) at the top left of your screen if it’s not already there:
For Excel 2007
- Office button
- Options
- Customise ribbon
- Choose commands from: Commands Not in the Ribbon
- Scroll down and select Camera
- Click Add
- OK
For Excel 2010
- Office button
- Options
- Quick Access Toolbar
- Scroll down and select Camera
- Click Add
- OK
Now you should see the camera icon in the QAT and you can use it as follows, having already prepared your embedded chart:
- Select the area behind your chart
- Click the camera tool icon
- Click on your worksheet where you want you graphic to appear
- Done!
You’ve now got a graphical copy of your chart and you can copy and paste it to your heart’s content. But there’s more!
Change your new Camera Tool Created Chart
Make a change to some of your data and watch what happens to the camera tool graphic … it changes too.
- Change a title on your chart
- Choose a different design for your chart
- Don’t move your original chart though … try it and see why!
The camera tool essentially creates a video of your chart so that whatever happens to it, the camera tool version will be updated
In addition to pasting your new graphic in Word or PowerPoint or elsewhere, you can paste your camera tool chart anywhere in your Excel workbook or file, even on another sheet. More than that, you can monitor how you chart is changing: this is brilliant if, for example, your chart starts in cell BZ10000 where it’s very difficult to access … now you can take a photo of it and put it where you like.
Security Aspects
The camera tool helps with security too: you can see it change but you can’t interfere with the original chart by changing the camera tool version.
Duncan Williamson
