Urgent requirement [calculate % chang over n periods // drop off / decline over n)

[Hypothermic2]

I'm working on a requirement for a document, and made a title to best reflect the content of computation. I want to know if/how I am calculating decline / drop off % over/compared to n values in the row.

I have 30 rows; and I want to calculate show significant change on the up side or downside. Example, there were 1000, 2000, 1458, 4563, 100, 300, 60, 0, 0

It would indicate that there was a significant change on downside over the last few periods compared to the entire row of the set of n values.

To this effect. Please comment on the proper way of describing what I would say/do or calculate. The data isn't hard to analyze, I don't know the most proper way to describe the needed metriks and cell computations.

Thanks!

 

[Hypothermic2]

I have completed most of the information from the sheet, and wanted to create a moving average that I could use for finding a decline of metrics values. I still don' know what to think of the n values before and the comparison change value. If you have any comments, please comment and I will consider your input in the future.

 

[Asala42]

As an aside, one built in feature you might find immediately useful is Conditional Formatting with Color Scales. This makes it very easy to spot up and downward trends if you're mostly interested in being able to spot these patterns. Data Bars are another similar approach, akin to charting your data without building an actual chart.

It sounds like you are also interested in quantifying trends. I'd probably use these 1-3 calculations
>AVERAGE or MEDIAN formula can give you a rolling historical value. Median may be better if you want to filter out the occasional spike
>A raw delta formula (eg =Historical - Current).
>A %Increase. =delta/historical will give you a %Increase or %Decrease from what was expected.

If you need anything more complex, you may want to post some sample data, especially if you have a specific desired output in mind.

 

[Hypothermic2]

As an aside, one built in feature you might find immediately useful is Conditional Formatting with Color Scales. This makes it very easy to spot up and downward trends if you're mostly interested in being able to spot these patterns. Data Bars are another similar approach, akin to charting your data without building an actual chart.

It sounds like you are also interested in quantifying trends. I'd probably use these 1-3 calculations
>AVERAGE or MEDIAN formula can give you a rolling historical value. Median may be better if you want to filter out the occasional spike
>A raw delta formula (eg =Historical - Current).
>A %Increase. =delta/historical will give you a %Increase or %Decrease from what was expected.

If you need anything more complex, you may want to post some sample data, especially if you have a specific desired output in mind.

[TABLE="width: 500"]
<tbody>[TR]
[TD]1000[/TD]
[TD]585[/TD]
[TD]488[/TD]
[TD]300[/TD]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5374[/TD]
[TD]4638[/TD]
[TD]2000[/TD]
[TD]1000[/TD]
[TD]400[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]463[/TD]
[TD]564[/TD]
[TD]1023
[/TD]
[TD]1200[/TD]
[TD]900[/TD]
[/TR]
</tbody>[/TABLE]


This is a data example. I was insight with the AVERAGE, and then change, % increase/decrease, but I am unsure of the exact formula to enter into the programme. I want to show an additional visual indication if a drop in regular historical performance. I would get a visual indication that row 1 has obvious anonamily, row 2 shows a dcreased/anaomility, and row 3 shows an no addressable concern at the moment.

 

[PaddyD]

To do this in a formula, you will need to define the scale of 'obvious anomaly'. This could be an absolute (e.g. diff by more than 1000) or relative (by more than 20%) measure. You will also need to define 'different to what?" e.g. the average of the whole series, the average of the series up to the last point, the standard deviation of the entire population, the usual difference between one number and the next etc etc...

As we don't know anything about what the numbers represent, it's a bit hard to suggest what the most appropriate benchmarks and deviations are - you'll need to do that thinking, then we can help you implement something.

 

[Hypothermic2]

1. I'm considering the relative measure such as 'decreased of 40%'
2. I'm just required to programme basic logic that would indicate significant change, as in point 1, day over day.
3. Right now, I have an indication for when repeated values are zero, but this only works when the metrok is zero, and not a more relative approach that accounts for a general day/day decrease that would indicate an error.

Note: The values represent pageViews, the number of loads a page receives, and we are attempting to install intelligence that will detect where there is a trend change towards the down side.

 

[PaddyD]

so something of the form:

=((dayN_views - dayN+1_views) / dayN_views) >=0.4

...would tell you if the next day's views are more than 40% lower than the previous...