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?
<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)
Much appreciate your valuable insight.
Best Regards,
ArunS
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)
Much appreciate your valuable insight.
Best Regards,
ArunS