Author: whizzexcel@gmail.com

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 Microsoft Azure Machine Learning Studio A drag-and-drop tool with a graphical user interface for building, testing and deploying predictive analytics solutions on your data.   SAS Enterprise 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 testing 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.   Mean The average value based on a variable of quantitative data.   Median The central value of a variable of quantitative data. Using the median instead of the mean lessens the impact 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 of 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 …