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 | |||||
701 | 32,140 | 16,548 | 39,329 | 26,812 | 5,272 | 41,084 | 26,864 | 26,864 | |||||
724 | 24,090 | 7,263 | 9,276 | 28,554 | 5,273 | 30,733 | 17,532 | 16,683 | |||||
713 | 19,943 | 1,680 | 2,406 | 27,848 | 6,361 | 43,203 | 16,907 | 13,152 | |||||
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. |
<colgroup><col><col><col><col span="2"><col><col><col><col><col span="5"></colgroup><tbody>
</tbody>