-
It sounds like less of an Excel problem (I think that part is easily solved using if statements and lookups or similar) and more an issue with clearly defining what you actually want to do in terms of replacement data.
You basically need to come up with a set of rules in terms of what you want to replace the ignored data with, e.g first option is to use current data, second option is one year before, third is ...
Once you've done that then you can do the Excel work which probably won't be too tricky. Admittedly, I'd be tempted to consider how useful the whole thing is (probably not what you want to hear), once you get to the stage of manually excluding and replacing your data because it doesn't fit the pattern then you've almost decided what you expect the output to be.
You are reading a single comment by @aggi and its replies.
Click here to read the full conversation.
aggi
Chalfie
@christianSpaceman
I think the problem is that in my (extreme) example there isn't anything to go with.
The person setting me the problem has thought, well, we'll just take the next available 3 months. But not thought about, "what if there are two spikes in the period that have to be reported, what then"?
@aggi
I think this is the same problem. What if the last year's data for the same point also has "ignore" next to it?
The most likely situation is that there will be a spike in each reporting period, but it will only be one spike (of variable length).
This is my problem with it, what do you replace the data with?
Do you use values for the same months but different years?
or
Do you use the next available months?
Personally, I wanted to create a chart with control limits for all the data and a separate chart with just the data excluded.