Smoothing Data for Step Chart (to show flat lines and major increases)

Mgroner

New Member
Joined
Oct 8, 2016
Messages
1
Hello,

I've searched far and wide, and would greatly appreciate guidance for a method of smoothing increasing/decreasing data that results in flat lines separated by major increases, similar to a step line (but each line contains multiple points). I'm trying to create a chart to help identify major changes in demand of a product and competitive product as the price gap between the two products changes over time.

The method of smoothing involves checking each demand value (using velocity as a demand metric) to see if it's greater than the previous demand. If so, a weighted average is used to smooth the SPM value. If the next demand value is also greater, the the weighted average is calculated again with the previous two values. Then that smoothed value is used for all three values. The process is repeated for each row, until the next values is less than the previous weighted average value.

The issue arises if a later smoothed value ends up being greater than the previous value, because then you need to go back and calculated a weighted average with the previous value again. I have included an image below for the step-by-step calculations. Column G represents the Previously, this has been done manually, which as you can imagine is just a brutal, time-intensive process.

[IMG]http://i64.tinypic.com/154zwyc.png[/IMG]
Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting

This results in the step line chart seen in the photo pasted below.
[IMG]http://i68.tinypic.com/1twkck.png[/IMG]
Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting

Any tips for simplifying this process would be awesome. Thank you very much advance!
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Watch MrExcel Video

Forum statistics

Threads
1,108,732
Messages
5,524,513
Members
409,583
Latest member
RedHelp

This Week's Hot Topics

Top