-
Love stuff like this. I think it's formula'able.
If ‘n in the include column’ is 1 or more (i.e. one or more months are excluded, due to an outbreak skewing the numbers), then the number of months excluded will be replaced by previous months in the ‘y in the include column’.
in the example above, if we wanted to look at the last 14 months we'd look from 01 01 15 - 01 11 13.
But there are "n" in Dec and Nov 14 and also Mar- Jan 14 (this is probably extreme, it's a random distro based on "randbetween" y/n to generate the numbers (as the cases were also randbetween 0-100).So in your example, there are five 'n's in the range to average. What would these be replaced with?
I think it's in here:
"then the number of months excluded will be replaced by previous months in the ‘y in the include column’"But can you specifically outline what 01-Dec-14, 01-Nov-14, 01-Mar-14, 01-Feb-14 and 01-Jan-14 would be replaced with.
You are reading a single comment by @christianSpaceman and its replies.
Click here to read the full conversation.
christianSpaceman
That table there is made up data.
I plot the number of cases as a line chart, with the time on x, cases on y.
what i've been asked is this:
the graph will show all cases from today's date to 24 months ago.
The user will put in a number on sheet one. This will be from 7 - 18, so they can look back 7 - 18 months.
this value will calculate an average number of cases (and also a +- 3.33) based on
that number
AND
if there is a "y" in column "include".
so far so simple.
however,
If ‘n in the include column’ is 1 or more (i.e. one or more months are excluded, due to an outbreak skewing the numbers), then the number of months excluded will be replaced by previous months in the ‘y in the include column’.
in the example above, if we wanted to look at the last 14 months we'd look from 01 01 15 - 01 11 13.
But there are "n" in Dec and Nov 14 and also Mar- Jan 14 (this is probably extreme, it's a random distro based on "randbetween" y/n to generate the numbers (as the cases were also randbetween 0-100).
I'm being asked to create a full 12 months of y to generate the lines i need. Now.
How can i build a window of 12 months to take an average from, I think i could do it two ways:
generate another table with >24 months in it, and then average only if "y".
generate one table with > 24 months in it, some sort of offset to create a dynamic window
any advice would be great...