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

Two New Cases

06 02 2015

Here are two new Excel based cases I have just prepared that I know some of you will appreciate.

UK Petrol Prices

I downloaded a file of all retail petrol and diesel prices for the UK from 2003 to 2014. The task is to prepare an interactive table so that the user just enters a date in one cell and the table then shows the

  • retail pump price per litre
  • duty per litre
  • VAT percentage
  • calculation of the cost of the petrol net of duty and VAT

This case involves the use of a variety of techniques including

  • VLOOKUP()
  • Data Validation
  • IFERROR()
  • Paste Special Multiply

Here’s an example of my output:

petrol_costs

 

This video summarises the case:

Sleep Requirements.

In today’s Borneo Bulletin newspaper there is an article on the sleep requirements of people of all ages. They give the requirements such as, a new born baby needs from 14 to 17 hours a day … someone over 65 years of age needs from 7 to 8 hours sleep a day.

The task is to turn a photo of the data into a table and a chart that communicates as effectively as possible: I prepared the following:

sleep_graph

sleep_table

 

Please feel free to write to me at any time to ask for the spreadsheets that accompany these cases. At the moment they are live cases and I won’t release them generally until the course has finished in a week’s time..

Duncan Williamson

 

Reverse Pivot Magic

11 11 2013

UPDATE: 16th January 2014: I have been using this technique since I last reported and I have found some limitations AND solutions to this reverse pivot technique. I will prepare a page and video to demonstrate what I have found.

2nd December 2013

Watch the video and then imagine that you have downloaded a five year set of financial statements for a company. Nothing wrong with the data or the database they came from but imagine you want to set up a pivot table from them. Probably not possible unless you are lucky enough to have a data provider who has designed their output with this in mind.

Let’s cut a long story short: open this file amazon_case_reverse_pivot and try to apply the reverse pivot technique in my video to the data you will see there. Good luck and it will be worth any effort you put into it! PLEASE READ THE NOTES in this work book re copyright …

I didn’t invent this technique but I learned it a couple of weeks ago and used it in earnest just now … watch the video!

I had a table that had months across the top, years down the left hand side and data in between. What I wanted was the data in a list in three columns:

year month value

transposing, rotating, copying and pasting can give you what you want but it will take a long time. My original table covered all twelve months and five years …

Reversing a Pivot table is what it’s called and this is how to do it:

  1. Press Alt + D then P to call up the pivot table wizard and select Create a “Multiple Consolidation Ranges PivotTable.”
  2. Select “I will create my own page fields”.
  3. Select your data range and choose 0 page fields
  4. When you see your pivot table, double click on the intersection of Row Grand and Column Grand, in the bottom right hand corner of your pivot table
  5. You will be presented with your new table in the form of a list … that is, from pivot table style to list style … it’s magic!

The video 

Duncan Williamson

%d bloggers like this: