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

 

An introduction to measures of central tendency: mean, median and mode. We use Excel to discuss this topic and there are three exercises in this presentation. http://ift.tt/1xS5gnV

The Find&Replace menu in Excel contains some hidden gems. Things that you might never have seen before. Take a look here to see what you’ve been missing. http://ift.tt/1JrisW3

This video shows you how to use the =CORREL() function in Excel with the Ctrl+Enter technique. This is especially useful for Mac users who do not have access to the Data Analysis Toolpak http://ift.tt/1yWlzFO

Happy New Year

31 12 2014

Hello everyone!

It is 20:35 on 31st December where I am and that means new year celebrations have started or are very shortly about to start somewhere.

I wish all of you a happy and peaceful new year. It is my resolution to post more pages here in the coming year. As you know, I post something as often as I can and I try to ensure everything is of high quality.

I have upgraded my screen capture video production software and intend to create and post more of them.

Do feel free to write to me at any time, by the way: I always reply to my messages and comments.

Best wishes

Duncan Williamson

Khan Academy

24 12 2014

I am learning all sorts of things this year and will continue next year as well.

One of my sources of learning is the Khan Academy and I recommend it to you for all sorts of things including mathematics … plus science and computing. It’s FREE too!

Here my link to take you there, please take a look.

 

Duncan Williamson

The menu system on this blog has fallen apart for some reason and until I get it sorted out I will communicate this way as well as through the home page and so on.

Anyway, two new videos in case you missed them that can be found in my OneDrive Public folder

how to create a positive and negative chart/graph. That is, making the positive numbers blue and the negative numbers red … or any colours you wish

Introduction to setting up a Pivot Table and drilling down in a Pivot Table. Pivot Tables are vital for anyone dealing with numbers and analysis and if you haven’t learned them yet … spend about 10 minutes with me.

You should see the respective Excel files there too!

Stop and start these videos as you wish until everything is clear.

 

Duncan Williamson

21st December 2014

%d bloggers like this: