Author: whizzexcel@gmail.com

Net Promoter Score

Net Promoter Score (NPS) is a popular method of measuring customer satisfaction when analysing feedback provided on a 1 to 10 scale.   An NPS result returns anything between 100 and -100 so it can be a useful mechanism for converting customer feedback scored on a 1-10 basis into a percentage, whilst at the same Read More …

Sunburst charts

A sunburst chart, available from Excel 2016 onwards, is a great way of visualising hierarchical attributes.   A simple example could be a company’s global sales, using a geographical hierarchy from continent, to country, to city with this dataset:     Select your data, go to the Insert menu and select Charts – See All Read More …

Skewness in Excel

Skewness is a measurement which helps you to understand the effect outliers are having in your data.   A positive skewness indicates a ‘right-skewed’ distribution where the majority of the values are on the left of the distribution and the outlier values are on the right. A negative skewness indicates the opposite. If a skewness Read More …

Data Science

DATA MINING Data mining The technology used for collecting, store, processing, transforming and analysing raw data in order to make it useful for gaining insights.     SOFTWARE Data visualisation software There are numerous programs for creating data visualisations and dashboard reports some of the most popular being: – Microsoft Power BI – Qlik – Read More …

Analysis

SIGNIFICANCE TESTING Hypothesis A prediction or statement about a characteristic of a variable, which can be tested to provide evidence for or against.     Significance test A method of statistically testing a hypothesis by comparing data against values predicted by the hypothesis. The significance test considers two hypotheses, the null and the alternative, effectively Read More …

Mathematics

CALCULATIONS Mode The most common value for a variable based on its frequency, can be calculated from either qualitative or quantitative data. The MODE function can be used in Excel to return this.     Mean The average value based on a variable of quantitative data.     Median The central value of a variable Read More …

Basic statistics

VARIABLES Qualitative vs Quantitative variables Qualitative variables are categorical items, whereas quantitative variables have a numerical value associated.Qualitative example: Blood groupQuantitative example: Temperature     Discrete vs Continuous variables Quantitative variables can be split into discrete (counted to an exact figure) or continuous, which can’t be measured precisely so need to be rounded.Discrete example: No Read More …

Refresh one connection at a time

In some cases you might find that the RefreshAll command isn’t working as you’d hoped. For example, if you have a pivot table looking up data from a SQL database connection you need the pivot table to refresh after the connection but RefreshAll won’t necessarily do that.   To ensure one connection is refreshed at Read More …