Histogram with fittingPosted: February 27, 2013
So, sometimes we have one-dimensional data set and we want to show graphical resume of what we have. If we want to visualize tendencies, distributions, populations present in sample histogram is probably our first choice. It could be applied to any type of data: circumference of trees, foot sizes, number of steps we de each day, clicks on page. In my case it was sizes of artificial particles found in fluid during hip prosthesis replacement/revision surgery.
How we proceed with this?
In spreadsheet are two tabs with pretty much the same. As input I have array of data from image program that automatically measured particle’s sizes on microscopy images and calculated some basic statistics. This data we have presented in columns “Analyze settings” and “Raw data” and “Statistical sum-up”. Those are our starting point.
Let’s go to “settings for plot”.
To make histogram I have to segregate it into the bins. Firstly we need to decide on bins number and/or width. There are number of methods how to do that (see e.g. wiki page http://en.wikipedia.org/wiki/Histogram ). In this case I do it in old fashioned way – arbitrarily. My goal is to make data look good. It’s actually not that bad idea – if you want to have nice bin limits without ridiculous fractions… So I start from 0 and pick value of width.
In next columns I calculate components to calculate number of particles in each bin. Those will be y-scale values. Of course one can put all the functions into one cell. But sometimes it is good to see whole procedure step by step. It is especially handy during debugging.
So I ‘have in order bin’s limits, Boolean condition-argument for COUNTIF function, number of particles below upper bin’s limit, and finally number of particles in bin.
We can start plotting graph.
I like resolving peaks (my other hobbies include data regularization and curve fitting). So always when I see multimodal distribution I fit it with a few bell curves just to see effect. This is not an exception.
As X values I use bin limits. Y values are calculated as standard bell curve with three additional parameters: average (bell’s axis) std. dev (parameter describing how broad/narrow is our bell) and height (bell’s height). Those values are calculated by Solver. As minimized function for solver I use, as always, sum of squared residues – that can be found in last column of this table.
Sometimes there are problems when distributions are narrow and bin sizes are wide. Situations when maximal size of bell curve is hap
What for?pen as an effect of best fit to the data. But sometimes as calculated average/middle/axis is in between of data points it couldn’t be seen on the plot. It is also reason why sometimes bells seem unsymmetrical. In those cases – make bins narrower.
Yes, you can do it in any program for plots (gnuplot, mathematica etc.). Advantage is that you do not need to thing about bin size or fitting function because it is often build in (or do you?). But this is Excel blog and its purpose is to show that you can do it in Excel.