Identification of variances between cell values

[JP Romano]

Good day,
I'm looking for some assistance in identifying "spikes" on a spreadsheet. I'm attaching a basic sample, with the data the way I hope to see it if I can get this to work (with some help, of course).

The user will have a form, into which a Spike value will be entered. That value will represent a tolerance percentage.
Some other factors:
There will be an unknown number of rows.
The spikes can be a single data point, or more of a plateau.
The pricing data can be negative (I have no idea how that works, but apparently, it's legit)

If any of you have an opportunity to take a look, and have some suggestions, I'd certainly appreciate it.

Thanks!

[zbor]

So is -100 a SPIKE or not?

According to your example this could work:

Please Login or Register  to view this content.

But this is very limited approach and I would like to see when it fail to adopt it.

[JP Romano]

ZBOR, yes, on the attached sheet, a -100 would be a spike (in my sad attempts to do this myself, I was using the absolute values, which may or may not be the appropriate approach).

Thanks for your time!

[zbor]

Maybe this:

Please Login or Register  to view this content.

[JP Romano]

Also, the 100% is just an example - the users may chose to use 75%, 150%, or even 200%... Apologize if I wasn't too clear, and thank you again for any time or help!

[JP Romano]

I've also tried stuff like this, which basically divides the higher number by the lower number, then the lower by the higher and sees if either is greater than the spike - but it's not working:

Please Login or Register  to view this content.

[JP Romano]

I've also tried stuff like this, which basically divides the higher number by the lower number, then the lower by the higher and sees if either is greater than the spike - but it's not working:

Please Login or Register  to view this content.

[zbor]

Write only 100% (or whatever) in C1 and use this:

Please Login or Register  to view this content.

[JP Romano]

ZBOR - thank you so much - I'm going to give that a shot and see if it captures the bulk of the problems we're seeing with the data.
I can see a situation where if the majority of the values in the range are bad this approach may not work, but I can't say for sure if that's a realistic expectation.
I thank you again for your time and wonderful suggestions!

[JP Romano]

ZBOR - thank you so much - I'm going to give that a shot and see if it captures the bulk of the problems we're seeing with the data.
I can see a situation where if the majority of the values in the range are bad this approach may not work, but I can't say for sure if that's a realistic expectation.
I thank you again for your time and wonderful suggestions!

[JP Romano]

Okay, I hate to sound like a PITA, but one fairly common data problem is when two days have the same value, but one is negative and the other is positive.
So if you were to change every value on my sample to 100, but one value to -99.23, the formula isn't catching it.
I know, it's like I need to use the absolute value SOMETIMES, but not all the time... ugh.

[JP Romano]

Thank you again for your time - using your ideas of medians/averages, I ended up going with a solution that averaged the prior 5 and following 5 days, if those days were non negative and had a reasonable variance from the prior day.