This Blog was initially called Excel 2007 with ExcelMaster. Time has passed now and we’ve got Excel 2016

Anyway, this Blog contains an ever growing set of detailed pages on How to … do things and pages of simple but very useful Tips.

19th August 2016: There is a post on Top Tips from my course this week … that’s a post and follows this section, below. There is a page on the How to Other menu called CAGR … RRI … what’s the truth? and that is aimed at finance practitioners and students.

19th June 2016: 1 How about finding the IRR of a Perpetuity? I came across a question on that on quora.com and answered it. I know I did not answer the question in the way the questioner was asking but I like my answer anyway! IRR of a Perpetuity in the How to … Other menu … here

2 Secret Graph and Graph Your Name. You can create graphs without having any data on a work sheet … did you know? That means you can put just about anything you like on a graph. It’s a bit dot matrix but it works and with your flair and imagination it could look magical! Excel How to charts menu … here

31st May 2016: You know you want to know how to prepare your own population pyramid in Excel. Well now’s your chance. This new page is on the Excel How to … Charting … … but click here to go straight there! 1st June 2016I have UPDATED this page and the Excel file …

25th May 2016: Do you use LINEST? Try this, then, if you want your output to have more flexibility. Combine INDEX and LINEST. See the page of that name under Excel How … Others … menu

2nd May 2016: a page about library fines … from almost 70 years ago … present values and so on. Menu … how to … other!!

11th April 2016: 1 Power BI … if you’ve never heard of it or seen it, you are in for a treat. Here is the first of what I imagine will be many pages on that topic. I give you here a basic example of how to import a CSV file into Power BI and how to analyse the data you import. It’s so simple to use and so powerful at the same time. You will find this page under the Excel How to … Other menu.

2 Here is a page for anyone who has or who is about to upgrade to Excel 2016: Forecasting in Excel 2016. You will probably e surprised by what you read as they have done something different this time. At the same time as including some new forecasting functions, they have included a utility to go with it. You will find this page under the Excel How to … Other menu.

14th March 2016: A Sparkline revelation. Go to the How to Graphs … menu to see a miraculous Sparkline. Sparklines … REALLY … truly exciting!

 

If you’d like my help and guidance on something, just ask and I’ll do what I can.

Duncan Williamson

Introduction for Absolute Beginners: Introduction to Excel for Absolute Beginners
Excel Files for Practice:
The basics of spreadsheeting 1
The basics of spreadsheeting 2
The basics of spreadsheeting 3
The basics of spreadsheeting 4

19th August 2016

I have just completed another very successful Financial Modelling course and as you know, at the end of such courses, I come here and offer something new: a new topic, a new file or some advice. In this case, it is advice: things that you really need to think about when you create and work on any Excel file.

  • Tab/Sheet Names
  • Links
  • Dead Cells 125,433 rows created but only 831 needed/active … files that balloon to many Mb for no real reason

Ever seen a tab name like this: FBU or OPT? I bet you have: short and sweet and probably mnemonic so easy to read and remember. How about L_P_Obasange_receivables_dont_forget_to PRINT_it_out? You think I am joking? I am serious! Just imagine you are working on your file with the large tab name and you want to link to a cell on that tab from another one: this is what will appear in your formula, by way of an example … =IFERROR(AND(A15=45,D26=”Jack”,L_P_Obasange_receivables_dont_forget_to PRINT_it_out!BA154 …

I am sure you see the point now. Keep tab names short and simple! More than that, if you do feel the need to use tabs to give instructions, colour code them to pass such messages: there are many colours to choose from so do that. Have a table of contents too. Give everyone a chance for a simple life!

Links

If you share a file with someone, make sure any links in your file are either live or delete them. If you receive a file with links that you cannot use or update, you know how frustrating it is. Think of the user before you send linked files.

Dead Cells

It is the easiest thing in the world to create a worksheet and as you work and improve what you are doing, to delete cells and ranges. We all do that. We create new ranges too, don’t we! Check your work now and again though and if these happen, take a break and check your file:

  • it takes 30 seconds 45 seconds or even longer for the file to open
  • what seems like a small file in terms of content and complexity has ballooned in size to 20 or 30 or more Mb
  • saving the file takes an age too

If these things happen, go to a worksheet and press Ctrl+End and see where that takes you. You work only in the range A1: CD831 but Ctrl+End has taken you to CG125433 … what? How did that happen?

Even if there are not as many as an additional 1.8 million cells but just 500,000, look in those cells for formulas  that are trying to find something from somewhere that is not there … in some of these extra cells for example. Delete all of these extra cells. I did that this week: an extra 1.8 million cells in TWO separate worksheets complete with formulas. File size down from 28 Mb to 0.8 Mb, opening time just seconds, recalculation time hardy noticeable.

They were just some of things to report on from this week. Otherwise, this group of delegates really enjoyed the work and their end of course presentations were interesting and showed that significant learning had taken place!

Duncan Williamson

 

Are you interested in using BI? Do you already use it?

Did you know that BI is free to create?

I will be adding some BI resources here this week … but there are already some here! Take a look at Power Query and Power Pivot for a start.

Back soon and if there’s something you want me to write about, let me know.

Duncan Williamson

A delegate asked me today if there was such a function as SUMDIVIDE. There isn’t of course, but I found a way to simulate it.

What would SUMDIVIDE do? SUMDIVIDE would have array 1 divided by array 2 and the results then added together: in the same way that SUMPRODUCT multiplies and then adds.

This is how it works: imagine A1:A5 contains array 1 and B1:B5 contains array 2 then the SUMPRODUCT function to divide them will be =SUMPRODUCT(A1:A5,1/B1:B5). Simple, eh? Who’d have thought it would be so simple.

Duncan Williamson

Time is just flying by and although I’ve got so many things to share time is against me.

In addition to this blog you might want to follow me on LinkedIn too … I don’t accept everyone there but if you tell me you subscribe to this blog I guarantee acceptance.

I am working in Dubai this week: presenting a three day course on Financial Modelling and Business Intelligence. Then a two day course on Budgeting and Cost Control.

With my courses you get a tool box of functions and techniques. You also get one to one time with me as I solve your problems … financial modelling etc! You take away all of my notes and PowerPoint slides as well as all of my fully worked Excel files.

I use real world data in my models and demonstrations. You get full explanations from me. I am also honest: if you ask me something I will give you the right answer, even if it means I have to do further research or ask a friend!

Find out where I am working and what I am doing and who knows, you might even join me one day. Invite me to speak where you are and maybe we can get together that way.

Duncan Williamson

New Video! –

12 12 2015

New Video! –

01 11 2015

Oil Tanker Problem

06 09 2015

Introduction

Last night I was finishing off some work when I came across a problem on a help site: the problem had been posted in December 2014 and hadn’t been answered. I realise that 9 months is a long time but I wanted to understand the problem and see if I could provide a solution: I did both!

One of the difficulties with the problem as stated was that the questioner asked for a nested IF solution, He insisted. Other people tried to help him and he duly provided a mock up of his worksheet for us all. Again he talked about a nested IF statement solution and even provided one … that he freely admitted didn’t work.

The problem is this:

  • there are two oil storage tanks and oil is first measured by means of a dip stick which is then used to estimate the amount of oil in the tanks
  • the oil is stored for days at a time and each day new oil is added
  • every now and again, though, the tanks are drained

He said that by taking the differences between opening and closing stocks for each day he can estimate the amount of oil added; and by adding together the results for the two tanks he gets overall additions for a day.

Good!

Then he says the problem comes when the tanks are drained … his calculation shows a negative result for dranage day!

I translated his graphic into a worksheet and here it is.

The question is: can you solve his problem?

oil_tank_problem

This table starts in B7 and in L11 there is a formula: =E11-E10+H11-H10

That formula is then filled down to the end of the table, day 31 in cell L41

You can see the problem clearly in the second part of the table, day 27 , where they have drained the first tank overnight:

oil_tank_problem2

That’s it: I solved this problem … can you? Let me know and I will share my solution. If you ask, you must tell me what you did to try to solve it. No try, no get!😉

Duncan Williamson

Follow

Get every new post delivered to your Inbox.

Join 258 other followers

%d bloggers like this: