# Smoothing of Peaks & Troughs , to generate a baseline forecast...

#### ArunS

##### New Member
I come to you all for your expert advise on how to remove peaks and valleys from a range of data.
I am working on averaging a range after smoothing the data.Below is the table I am working and please refer the formula given. Is the approach fine?

 SKU_ID Sum of Jul-14 Sum of Aug-14 Sum of Sep-14 Sum of Oct-14 Sum of Nov-14 Sum of Dec-14 Average shipped Avg. w/o peaks/valleys 0701 32,140 16,548 39,329 26,812 5,272 41,084 26,864 26,864 0724 24,090 7,263 9,276 28,554 5,273 30,733 17,532 16,683 0713 19,943 1,680 2,406 27,848 6,361 43,203 16,907 13,152

<tbody>
</tbody>

Formula used for comouting avg w/o peaks & valleys =IFERROR(IF(MAX(L3:Q3)>1.6*AVERAGE(L3:Q3),QUARTILE(L3:Q3,2),AVERAGE(L3:Q3)),0)

Best Regards,
ArunS

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
I am eagerly awaiting your all insight into this logical approach. I might be overlooking something that possibly you can advise or suggest. This is very important for me and would sincerely appreciate your feedback.

BR,
ArunS

I come to you all for your expert advise on how to remove peaks and valleys from a range of data.
I am working on averaging a range after smoothing the data.Below is the table I am working and please refer the formula given. Is the approach fine?

 SKU_ID Sum of Jul-14 Sum of Aug-14 Sum of Sep-14 Sum of Oct-14 Sum of Nov-14 Sum of Dec-14 Average shipped Avg. w/o peaks/valleys 0701 32,140 16,548 39,329 26,812 5,272 41,084 26,864 26,864 0724 24,090 7,263 9,276 28,554 5,273 30,733 17,532 16,683 0713 19,943 1,680 2,406 27,848 6,361 43,203 16,907 13,152

<tbody>
</tbody>

Formula used for comouting avg w/o peaks & valleys =IFERROR(IF(MAX(L3:Q3)>1.6*AVERAGE(L3:Q3),QUARTILE(L3:Q3,2),AVERAGE(L3:Q3)),0)

Best Regards,
ArunS

Replies
0
Views
316

1,196,481
Messages
6,015,451
Members
441,896
Latest member
clomah

### 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.

### Which adblocker are you using?

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

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