Excel Assitance - Average Sales and Identifying spikes

[Dolby]

I get a report to reorder certain stock for the next month.

It reports actual sales for the last 6 months, simply as : 4 , 6 , 3 , 50 , 6 , 3 and gives me an average to order for the month - in this case 12. But I manually check, notice the spike (once off project deal) manually exclude this figure and order 5.

Now I have 100 of these products with potential spikes - do you think there is a formula to indicate a spike? Doesn't need to be dead on accurate, but would be great if it could highlight items with a potential spike.

Maybe something calculate median and mode, and somehow use those?
If the difference between the mean and the mode, could indicate a spike?

Looking for suggestions, please

EDIT : Or a standard deviation ?

 

[feo]

You could work out the standard deviation and count if there is a month which is higher than 2x or 3x the standard deviation and only claculate the average of the non-outliers.

Or you can try the TRIMMEAN function.

 

[Dolby]

TRIMMEAN ... I'll look into, thanks !

 

[LaraC]

Conditional formatting will allow you to highlight potential spikes but it will be time consuming to do.

You can use this formula to identify possible spikes from the median.

=MAX(A1:F1)/MEDIAN(A1:F1)-1

Edit: The formula assumes your data is in cells A1:F1 and the formula in G1. The result will indicate by how much the median are exceeded.