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

21st April 2017: CLEAROUT I am having a clearout! There are many people who subscribe to this blog but they are lurkers only. I would be grateful if you would state your preference for continuing to be a member of my community by saying hi! A simple email or comment will suffice and I will leave you here. Otherwise over the next month or so I will delete you.

21st April 2017: My Excel Solutions for Accountants books: I have just completed some updating work on this three volume set and this is what my readers have said about them:

  • Absolute solution to Accountants who have not used the excel experience in doing calculation and graphs on financial data analysis
  • The book is really fantastic, a must read for professional accountants.

The books are available from my page here: www.bookboon.com

21st April 2017: A page on Data Analysis. I have taken data on the human body among other things and analysed them as worked examples. There are four cases to study and few all of them you get the data only excel file, the fully worked Excel file and a PDF with my notes and explanations. There is a further six cases that I have worked through and that you will find in both Excel files but there is no analysis in the PDF file: these are for you to complete. I have put this in the Excel How to… Other … Functions menu but you can go straight to it from here.

6th April 2017: A really useful insight this time: Pivot Charts from Power Pivot even Without Pivot Tables. Take a look at this page and download the PDF file that comes with it. Sorry but I cannot share my Excel file with this one. Excel How to … BI sub menu.

3rd April 2017: This is something new, using Microsoft Sway to format a file for me. I know what it looks like in the software itself but what happens when you go here … EPL Analysis and click the Sway page link … at this stage I don’t know. My analysis is good so let’s see what Sway an do! This page is in the How to … menus too!

2nd April 2017: Rice … some very interesting and useful data gets the Power Query Unpivoting treatment. I have discussed upivoting data before but here is a very practical example that uses international data on rice. You can go straight to the page I have created or go to the Excel How to … BI sub menu and look for Unpivoted rice

22nd March 2017: and here is a page for Power Query lovers and learners. Learn how to use a conditional column to determine labour turnover or what Chandoo calls churn! Go here!

17th February 2017: this is a biffy. Programming linear and non linear formulas using LINEST. The page for that is here and it takes concentration but is otherwise not too bad to do! Menu Excel Tips … Other Tips

3rd February 2017: A bit late but happy new year everyone. Here is a post for anyone who is new to Power Query. I came across  question on Quora that is best answered by using Power Query’s merge feature. Sagar Malik answered the question and I reworked Sagar’s effort and added one change … by reading Sagar’s answer and mine you have the full monty! Thank you Sagar for your insights.

30th December 2016: Click on a slice of a pie and reveal a column chart … search the web for this and you will come across some fancy things like chart event … no need for that. Excel How to … Charting menu to see this page: https://excelmaster.co/pie-to-column-chart/

29th December 2016: an analysis of the heights and weights of English Premier League footballers. The main text is contained in a PDF file that is free to download and there is a free Excel file for you too. I put this in the Excel How to …  Excel Functions menu and here is the direct link anyway: https://excelmaster.co/epl-player-heights-and-weights/

24th December 2016: How to shrink a formula by 1138 characters in one cell. I just answered a question on Quora. The person wanted to know if this could be simplified: =IF(B2=”IDR”,C2,IF(B3=”IDR”,C3,IF(B4=”IDR”,C4,IF(B5=”IDR”,C5,

IF(B6=”IDR”,C6,IF(B7=”IDR”,C7,IF(B8=”IDR”,C8,IF(B9=”IDR”,

C9,IF(B10=”IDR”,C10,IF(B11=”IDR”,C11,IF(B12=”IDR”,C12,

IF(B13=”IDR”,C13,IF(B14=”IDR”,C14,IF(B15=”IDR”,C15,

IF(B16=”IDR”,C16,IF(B17=”IDR”,C17,IF(B18=”IDR”,C18,

IF(B19=”IDR”,C19,IF(B20=”IDR”,C20,IF(B21=”IDR”,C21,IF(B22=”IDR”,C22,

IF(B23=”IDR”,C23,IF(B24=”IDR”,C24,IF(B25=”IDR”,C25,IF(B26=”IDR”,C26,

IF(B27=”IDR”,C27,IF(B28=”IDR”,C28,IF(B29=”IDR”,C29,IF(B30=”IDR”,C30,

IF(B31=”IDR”,C31,IF(B32=”IDR”,C32,IF(B33=”IDR”,C33,IF(B34=”IDR”,C34,

IF(B35=”IDR”,C35,IF(B36=”IDR”,C36,IF(B37=”IDR”,C37,IF(B38=”IDR”,C38,

IF(B39=”IDR”,C39,IF(B40=”IDR”,C40,IF(B41=”IDR”,C41,IF(B42=”IDR”,C42,

IF(B43=”IDR”,C43,IF(B44=”IDR”,C44,IF(B45=”IDR”,C45,IF(B46=”IDR”,C46,

IF(B47=”IDR”,C47,IF(B48=”IDR”,C48,IF(B49=”IDR”,C49,IF(B50=”IDR”,C50,

IF(B51=”IDR”,C51,IF(B52=”IDR”,C52,IF(B53=”IDR”,C53,IF(B54=”IDR”,C54,

IF(B55=”IDR”,C55,IF(B56=”IDR”,C56,IF(B57=”IDR”,C57,IF(B58=”IDR”,C58,

IF(B59=”IDR”,C59,IF(B60=”IDR”,C60,IF(B61=”IDR”,C61,IF(B62=”IDR”,C62,

IF(B63=”IDR”,C63,IF(B64=”IDR”,C64,IF(B65=”IDR”,C65,IF(B66=”IDR”,C66,

“None Here”))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))). I gave him this alternative: =VLOOKUP(Specific,B2:C93,2,0). From 1166 characters to 28!

19th December 2016: two pages today: sampling every nth row and reverse exponential smoothing. Two screenshot driven pages: one with  an Excel file and one without.

19th November 2016: obesity is a disease and this page illustrates the extent o that disease in England.

9th November 2016: I have updated my depreciation model again. You will find the original pages in this blog under the NEW menu sub heading of Accounting. In addition, my new page is there too. In truth, it concerns he depreciation of cars in Bangkok and in Melbourne. I am changing my menu structure by adding new headings … you will see more clearly as I move things around and the menu shrinks in length.

25th October 2016: As promised last week at the end of my seminar in Manila, I have added a multiple worksheet scenario model: it seems to work quite well. Let me know if this meets your needs! The page is here! Excel How to … Other menu …

8th October, 2016: Go to the How to Menu … Others to learn about Flash Fill courtesy of some GIFs. Flash Fill should be a massive time saver for you and it could mean that you never need to sue LEFT, MID and RIGHT again.

4th September 2016: A second page on life expectancies. In this case, I don’t share the file with you, unless you ask for it; and what this page concerns is the interpretation of a correlation matrix of life expectancies by country and then by a series of potentially explanatory factors. How to … Other menu … here

19th August 2016: There is a post on Top Tips from my course this week … that mean it’s a post and not a page so it follows this section, below … scroll down to read it! 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

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

 

I know I learned this function a while ago but then I forgot about it. The aggregate function was newly created for Excel 2010 and it provides us with some interesting functionality: overcoming shortfalls in the performance of such functions as AVERAGE, MIN, MAX, LARGE, SMALL and a few more … 19 of them altogether. The trouble is, good as it is, it is not that simple to apply. What I have done then is  to create templates that will help you to use the aggregate function in one or both of the formats in which it might be used.

From the Excel Help File:

Returns an aggregate in a list or database. The AGGREGATE function can apply different aggregate functions to a list or database with the option to ignore hidden rows and error values.

Syntax

Reference form

AGGREGATE(function_num, options, ref1, [ref2], …)

Array form

AGGREGATE(function_num, options, array, [k])

The AGGREGATE function syntax has the following arguments (argument:

Function Number

Required. A number from 1 to 19 that specifies which function to use

Options

Required. A numerical value that determines which values to ignore in the evaluation range for the function

Ref 1

Required. The first numeric argument for functions that take multiple numeric arguments for which you want the aggregate value

Ref 2

Optional. Numeric arguments 2 to 253 for which you want the aggregate value

For functions that take an array, ref1 is an array, an array formula, or a reference to a range of cells for which you want the aggregate value. Ref2 is a second argument that is required for certain functions. The following functions require a ref2 argument:

  • LARGE(array,k)
  • SMALL(array,k)
  • PERCENTILE.INC(array,k)
  • QUARTILE.INC(array,quart)
  • PERCENTILE.EXC(array,k)
  • QUARTILE.EXC(array,quart)

The AGGREGATE function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using option 1, such as AGGREGATE(1, 1, ref1), hiding a column does not affect the aggregate sum value. But, hiding a row in vertical range does affect the aggregate.

My templates will tell you if you are trying to use the wrong format of aggregate and has then been set up to ensure you don’t make any other mistakes when using it. This includes providing combobox guidance for choosing which functions and options you need to use.

The Excel file is available here: aggregate_function … just click!

All feedback is warmly received as are suggestions for improvement.

Duncan Williamson

Wildcards

As I am working away this week, I can be found looking at, opening up, working on a spreadsheet or two. Last night I came across yet another excuse to build an Excel data set. I found the TopTrack 250 and just had to have it: all seven years’ worth of day for the top 250 companies according to the Top Track definition. Look here for the 2013 data set.

So I duly copied and pasted the data for 2013 into a spreadsheet and edited it. Having seen that I liked what I had done I then copied over the other six years’ worth of data from Top Track. Because the data copied over in a very standard and predictable way, I was able to select all six sheets of the additional data and work on them all concurrently: moving data from one row to another, setting up columns in a better way, lining up the data to make it consistent with what I had already done with the 2013 set. Easy and not too time consuming.

But now the wildcards. On the web site that the data came from they had symbols such as asterisks that pointed to a note that said … average, estimate … or something like that. I needed to get rid of them all! As it was late and I wasn’t looking carefully, I carried out an edit and replace of * and having pressed Replace All, found that ALL of my data disappeared … it was doing what a wildcard must do … in this case, any character was being replaced by nothing! A very hasty Ctrl+Z put everything back to the way it was but I still had to get rid of these symbols.

What to do? Delete every symbol manually? No, there were too many of them. Given that the symbol was in the same place in the cell it was in, at the beginning, I ended up with a formula solution that I entered in a new blank column that I would eventually use to replace the current data:

=IF(LEFT(D6,1)=”*”,RIGHT(D6,LEN(D6)-1),D6)

Why D6? That’s where the first of my data points is in each of the tables I set up. I then copied the formula across to the next cell on the right and then down to the bottom of the table … it worked a treat.

I remembered that I should have added that there were other symbols in the data in addition to the * and I amended my formula to cope with the ‡ symbol:

=IF(OR(LEFT(D6,1)=”*”,LEFT(D6,1)=”‡”),RIGHT(D6,LEN(D6)-1),D6)

Now I had to copy and paste my edited values as text rather than leaving them in formula format, otherwise I have to leave the original data in the file AND have the edited version. So I just copied and pasted the edited values over the original data and my data were now clean and tidy … but read on!

Numbers as Text

One problem remained: having replaced these symbols, the result was numbers as text rather than number as number. In this case I did this:

  • enter 1 in any available cell
  • copy that cell
  • select the entire range of numbers … all of them so you miss nothing and don’t waste your time selecting just the affected ones if your data set is large like mine
  • paste special, multiply
  • ta daa! your numbers as text have now become numbers as numbers
  • Delete that cell with 1 in it

There you are wildcards and paste special to set up a nice, clean table of data copied from the web.

Duncan Williamson

%d bloggers like this: