28 06 2020

28 06 2020

28 06 2020

28 06 2020

28 06 2020

Answer to Which 20% Excel formulas give 80% of the results for data science and BI analysis? by Duncan Williamson

Quora Questions

05 11 2019

I write answers on various Excel topics at and this page provides links to some of my answers. They are not normally massively complex questions but I answer the ones that I think might be interesting to my visitors and readers. The pages are probably in reverse order: lastest pages first; but that should not matter!

Update 5th December 2019

How do I convert F to C in Excel?

Can I sort a column of strings in an Excel doc alphabetically by last rather than the first character?

How can you take a profit and loss statement in CSV format and easily load it into PowerBi?

Every CSV I’ve ever tried to save from Excel (2010) says “filename.csv may contain features that are not compatible with CSV (comma delimited). Do you want to keep the workbook in this format?” Why is this? How can I save it as CSV?

Why does Microsoft Office make it so difficult to guarantee your work is saved?

How do I extract consecutive capitalized words from a body of text in Microsoft Excel?

In Excel Wizards, is there a function that enables me to choose specific texts in a cell based on other keywords (i.e., ‘age: “29”‘)?

Why doesn’t Excel recognise comma separated values as separate and filterable?

What is an effective way to categorize and sort large lists of quotes or definitions for someone with limited Excel knowledge?

How do you graph 10,000 data in an Excel chart?

What is paste special in Excel?

What is the x-intercept and y-intercept of two lines x-3y=7 and 2x-5y=4?

What is NPV and its formula?

How can I make a Google Sheets function (in column K, row 5) that compares two rows (4 & 5) in the same column (column J) and then automatically skips 2 rows instead of one when dragged into row 6, so that now it’s comparing rows 6 & 7 in column J?

How can one have two things on the same axis in Excel?

This page is on my facebook page and not Quora! The page shows a Power BI image of the flight path of LY1747 of El Al … it is unusual, to say the least.

What are some real life examples and applications of multiple regression?

How do you show a subtotal at the bottom of a pivot table without showing the title at the top in Excel?

Which duplicate does Excel remove?

How do you use an exponential equation you get from Excel? I know how to make Excel show me the equation, but how do I “copy” it or use it?

How do I paste a chart from one Excel to a blank Excel?

What are the most unused excel formulas?

What are some typical statistics and plotting made on datasets with 2 columns of numerical values and one column of categorical values?

In MS Excel, I have a large column of times (in seconds and minutes) when events occurred. Is there a way to automatically segment this data to make a frequency distribution table of say 0-5 minutes had 3 events, 5-10 minutes had 0 events, etc.?

Journalizing records data chronologically in accounting. What is the explanation of it?

What is exponential smoothing in Excel?

I just came across a couple of pages on that you need to know about and forgive me if these are behind a paywall: I subscribe to the HBR so I went straight to every page.

Firstly, 10 Excel Functions Everyone Should Know by Adam Lacey and Deborah Ashby. I prefer DGET to their INDEX-MATCH function but otherwise, this is a good start to learning the top 10. Top 10 Excel Functions

Then there is a REALLY useful PDF file The Definitive 100 Most Useful Excel Tips at

Finally and following on from the 100 must useful Excel tips, Marc Zao-Zanders gives us this, A 2×2 Matrix to Help You to Prioritise the Skills to Learn Right Now, here:×2-matrix-to-help-you-prioritize-the-skills-to-learn-right-now This 2×2 matrix puts the 100 tips on a grid/graph to illustrate four quadrants:

  • Decide whether you need to learn it
  • Learn it as the chance arises
  • Schedule a block of time for learning it
  • Learn it right away

Marc says this, “You’ll find the quickest wins in the bottom-right quadrant, which we’ve labeled “Learn it right away.” In here we have time-saving shortcuts that can be applied frequently, like Ctrl-Y (redo) and F2 (edit cell) and a nice combination formula that cleanses your spreadsheet of errors (IF(ISERROR)).” Of course, IF(ISERROR) is way out of date and should be replced by IFERROR()

Duncan Williamson

22nd December 2018


If you have stumbled across this page hoping to find a cheap Business Class seat on Singapore Airlines (SIA), sorry! What you have arrived at is a page that explains how I found a superb result to my analysis of the last 19 years of SIA’s financial results.

The Data

As I was using Power Query to help me to analyse SIA’s financial results, I downloaded their last 19 years’ worth of Annual Reports and Accounts and from there I downloaded everything you see here:


The question is, what can I do with all of that? What I often do as part of my financial analysis is to create a Net Income model. That is, I make Net Income the dependent variable and have one or more independent variables.

The First Model

My first model comprises Y = Net Income  = All Other Variables in the above table, all six of them. I used the Data Analysis ToolPak for this and here is my output:


A Correlation Matrix of all variables and I have conditionally formatted the results to highlight the extreme values and the mid range values. For example, Revenue and Fuel Costs are very highly correlated as are Passengers and Rev Pax Km (Revenue Passenger Kilometers). Rather oddly, there is virtually no correlation between Staff Costs and the Number of Employees.

The Regression output are as follows:


Let’s note that the Adjusted R Squared value is high at 0.6755, the F statistic is significant at 7.25 but of the six independent variables only Staff Costs are significant, with a t statistic of 3.3287 and a P Value of 0.0060.

I think this is a superb result: Staff Costs being the only significant variable as its coefficient shows that a unit change to Staff Costs leads to a 2.5913 million SGD increase in Net Income.

The residuals plots show that everything is probably fine although we are only dealing with 19 data points or years of data:



On the one hand, this model fails because only one variable is significant and using just Staff Costs to predict Net Income is not rational. This says that we need to refine or replace the model to find something better. That being true, why do I think I found something superb?

SIA is famous for its recruitment of and investment in high quality cabin crew and other members of staff. Whilst they don’t pay the highest salaries, they do reward well overall, training is high level and frequent, they use more cabin crew per flight than most, if not all, of its competitors to maximise customer satisfaction.

Out of all of that, the data contain the relationships that I just mentioned and regression analysis has brought them out! That is superb in my opinion!

I want you to replicate my work here so, whilst there is a spreadsheet to download, it only contains the data I have extracted and used. Feel free to ask for advice and guidance here but I am not providing the full spreadsheet file.

Download the file of data from here sia_data_blog


Duncan Williamson

14th August 2018


%d bloggers like this: