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

Hypothermic2

New Member
Joined
Mar 23, 2017
Messages
4
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!
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Hypothermic2

New Member
Joined
Mar 23, 2017
Messages
4
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

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
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

New Member
Joined
Mar 23, 2017
Messages
4
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.

100058548830040
5374463820001000400300
10004635641023
1200900

<tbody>
</tbody>


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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

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

New Member
Joined
Mar 23, 2017
Messages
4
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,123,177
Messages
5,600,160
Members
414,367
Latest member
dw970906

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top