## SUMDIVIDE … sort of

### 08 03 2016

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

## Are you Following me?

### 07 03 2016

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?

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:

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

## New Video! –

### 19 07 2015

## Excel Solutions for Accountants

### 06 04 2015

That is the title of my new book on Excel and here is the Introduction to the three volume set: a total of about 200 pages.

# Introduction to Excel Solutions for Accountants

Duncan Williamson

April 2015

**This book will be published by ****www.bookboon.com**** early in June 2015 and of course this introduction is subject to change between now and then.**

This is the first in a series of three books with the title of *Excel Solutions for Accountants*.

The essence of the book is that we have chosen a series of topics that we believe are of direct interest and relevance to accountants: we know that from the work we do every day. That is not to say that everything an accountant ever needs or does is included in this book; rather it’s a general book aimed at the accountant who knows that Excel can be made to do a lot more than it does but he just doesn’t know what that might be.

We have taken a very hands on approach with this book and for everything we talk about there is something for you to do: there is a spreadsheet for you to work with, too, so that you can always check your work and your accuracy at every stage.

One of our over riding ambitions was to make this book both as direct and as easy to use as possible. You will not find massive files with hideously complex formulas in them, each of which might take you an hour or two just to begin to unravel. What we have done is to give you straightforward examples with non complex lists and databases so that you come to learn the techniques and functions rather than worrying about the database.

**Part One** of the book comprises the following

- Accountant Specific 1
- Excel Tables
- Depreciation
- Ratio Analysis 1
- Graphs 1
- Pivot Tables 1

You can see immediately from the titles of these chapters that the accountant is the target here!

Secondly you should see that four of the chapters have the number 1 after them: that tells you that in books 2 and/or 3 there is another chapter or chapters on the same topic. Take a look at the contents of books 2 and 3:

Part Two |
Part Three |

Accountant Specific 2 | Accountant Specific 3 |

Compound Interest and Discounting: the time value of money | Dashboarding |

Data Validation and Form Controls | Pivot Tables 3 |

Ratio Analysis 2 | Forecasting |

Graphs 2 | Budgeting |

Pivot Tables 2 | AGGREGATE, OFFSET and SOLVER |

**More than one way to skin a cat!** We present one or more solutions to the problems we present in this book and from time to time we say: Excel provides more than one way to solve this problem. If you already know a better way to solve a problem than we are presenting, stay with it. Even if you think our method is better but are happy with your own method, stick with it if it doesn’t mean you are wasting time or being inefficient. Don’t be stubborn though: like the cost accountant who saw our solution to his problem but preferred not to follow our advice: that meant he preferred to wait 20 minutes every morning for his main Excel file to open and then wait a further 45 seconds or so **every time he pressed the Enter key**. Our solution meant no waiting time at all as we replaced his 30,000 volatile function workbook with a Pivot Table based solution that provided almost instant responses.

**Templates**: we have tried at every stage of every chapter of this book to provide templates for you to work with and/or create. After all, what’s the point of programming a spreadsheet over and over again when you shouldn’t have to? Excel deals with some of the most predictable things you can do and templates are consistent with that. Therefore, take each of our examples either as a template or as your template in the making. We stress **PPP** too: **paper, pencil, plan**. The PPP approach means, don’t just dive in to a spreadsheet problem, take you time and plan it out on paper first. Then develop your solution. Then derive your template if appropriate.

**A total of 18 chapters** and by the end of all three books we believe you can easily call yourself an intermediate user of Excel. We also feel that you will have unlocked so much potential in Excel that you will want more and more from it.

This book has been written with **Excel 2013 for Windows**: that will mean that some of the things we have done will be a little bit different sometimes from what any other version of Excel might do. We have kept such differences to a minimum, however. Nevertheless, we would encourage you to upgrade to 2013 soon anyway.

**Other sources of help for Excel**: there are many sources of help other than Excel itself. Here are just a few examples of where to get help: just search for these online to get there!

**excelmaster.co**: my own Excel Blog**Excel-G**: online/email based discussion list for general level questions on Excel. There are about 1,000 members of this group and they are friendly and really helpful people. Please note, this is a general level list and anything too advanced should be directed at …**Excel-L**: online/email based discussion list for Excel Developers. This is the more advanced list and seems to concentrate mainly on VBA problems and solutions.**OzGrid**: this is a free and commercial site offering some brilliant resources.**chandoo.org**: this is another really useful site whose founder, Chandoo, has the objective of making you*awesome in Excel*! A lot of the materials on this site are free but some of them are commercial. Chandoo also offers online and offline courses.**Jon Peltier**: Jon seems to be the world’s foremost authority on graphs and charts. Again, this is a combined free and commercial site but there are many wonderful free resources on there.**Mr Excel**: Bill Jelen is a prolific Excel materials developer and explanator! Bill provides a lot of free and highly commendable materials, again both free and commercial.**Charley Kyd**: Charley has specialised in the past in dashboarding and whilst he still does that, he offers a lot of free Excel based materials too. Well worth searching out.**John Walkenbach**: John has been around for ages and he writes the*Excel Bibles*, among other Excel based titles. John is always comprehensive and has a lot of useful things to offer**YouTube**: there are many thousands of videos on YouTube now and more appear every day. Some of them are really very good and others will leave you wondering what on earth they were trying to say. It’s pot luck really but once you find a good YouTube provider, stick with them!

There are many more people/organisations/lists that can help you so these are just a few of the ones we recommend.