You are reading a single comment by @Chalfie and its replies.
Click here to read the full conversation.
London Fixed Gear and Single-Speed is a community of predominantly fixed gear and single-speed cyclists in and around London, UK.
This site is supported almost exclusively by donations. Please consider donating a small amount regularly.
@Chalfie
Yeah. Ok. The average if is fine, even average ifs are fine. I'm king of the ifs!
I'm trying to count the number of things and then average them.
We're making a control chart, so you bang in the data in a table. Put a date in one cell, the start of the reporting period, and then say in another cell the number of months you want to go back. This number filters a table on another sheet.
From this table you get the average number of cases and the upper and lower links (* 2.66 for a standard dev). A chart is plotted showing the number of things per month and the average ucl and LCL for the time period in the table.
Now, in the table you originally bang the data in you can say "y" or "n" to include it in the average or the limits.
This is all quite easy.
However, if the person putting the data says "n" to a data point, it should be included in the chart of things per month. But not in the calc for average or ucl/LCL.
Instead the average and the limits should use values from a previous period in time. (I'm not happy with this, but apparently it's ok to say "this is a spike, these can be replaced with what we know to be normal data"). Apparently, if the time period were looking at is Jan 2014 - mar 2015. And if the months Jan - mar 2014 should not be included, we cAn substitute in data from 2013 that can be included. For example the months Oct - Dec.
I guess what I could do is:
Filter a new table with 24 months of data, plot the number of things per month using the time ranges entered on the first sheet.
And then this is where I fall down, if the table is big enough, averaging if will be ok.
There's a lot of confusion here as I'm not sure this is sensible, and what happens if there's a hige run of data missing, or patches of data. Where is this imputed from?