IF statement to remove sales peaks and dips from dataset

[Vince-S]

Hi,


I am trying to clean datasets on a large scale, I want to remove any dips <20% and spikes >20% than the previous value, if any condition is met I want to take an average of 1 week back and two forward. I have tried several options like using formula's using IF(AND( & IF(OR( but they always seem to give one result back sometimes TRUE sometimes FALSE.


In short:

IF ANY VALUE IN ROW 3 IS 20% >or< THAN IT PREVIOUS VALUE THAN TAKE AVERAGE OF 1 WEEK BACK AND TWO WEEKS FORWARD IF NOT THAN POST ORIGINAL VALUE.


Thanks,

Vince

 

[vasim]

Since you wnat an average of 1 week back I am assuming your formula column will start in B7


past 1 week = 7 days

next 2 weeks = 14 days


The formula would be

=IF(A6/A7<>20%,AVERAGE(OFFSET(A7,-6,0,14,1)),A7)

adjust according to your requirement.

 

[Vince-S]

Thanks, it works!

 

[Hui]

Shouldn't the average of 1 week back and 2 weeks forward be:

=IF(A6/A7<>20%,AVERAGE(OFFSET(A7,-6,0,21,1)),A7)