## Business Intelligence

### 30 03 2016

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

## 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