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 …

# Author: whizzexcel@gmail.com

## Data Science

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 Miner A solution for creating accurate predictive and descriptive data models using data mining and statistical techniques such as linear regression, clustering and classification (decision trees). 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 …

## Histograms in Excel

Histograms can be used as opposed to traditional bar charts when you are dealing with continuous variables. Particularly when there are a large number of values on your x-axis. With a histogram you can group together the values on your x-axis into “bins”, usually of equal sizes. The output will be very similar to a 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 …

## Multiple Find & Replaces

When you are data cleansing within your VBA routine a common action you might need to do is re-map text values to a different name so they match another dataset you’re joining them to. You can do this in VBA without having to specify each individual value in the code. In this example we Read More …

## INDEX and MATCH functions

INDEX and MATCH are two of the most versatile functions within Excel. We will look at these functions individually to begin with but the real value comes from combining the two of them. They can serve as a useful alternative to VLOOKUP because they can look up in either direction – VLOOKUP is restricted Read More …

## Power Pivot – Drill down to more than the first 1,000 rows

When you double-click a value on a Power Pivot table to view a listing of the results, by default the pivot table will only return the first 1,000 rows. To expand this limit, go to the Data tab and click on Connections. In the dialogue box, click on ‘ThisWorkbookDataModel’ and go to Properties. In Read More …