So you just need to get the Average of that column. You will have the data without the peaks in the D column. So, assuming your sales data is in the A column, you calculate the average of all values:īeing the deviationFactor a number that controls how high the peaks have to be to be removed.Ĭopy this formula to cells D2:Dn, taking care only the index in A is changed (average is always in B1 and standard deviation in C1, but original data is in A1:An). You probably want to exclude only highest peaks, and I think an easy way to accomplish that is using the average and standard deviation. In Excel you can use TRIMMEAN, but that will remove also the lowest entries in your distribution. Other option, as already mentioned is to exclude outliers from your sales distribution. That way the query would exclude promotion days. In order to exclude promotion days per store I would have a look at those store APIs to check whether they have a promotion API, and store that info in your database. Maybe this isn't possible, but I figured I'd ask. This doesn't have to be in SQL, that computation will most likely happen in a spreadsheet, or could happen in the report generation if it's too complex. I'm looking for a way to take an average that removes spikes that are well above the average. Many journals ignore former conventions and permit all sorts of trend lines and. I just want to see an average for average non-sale days and I don't want to go in and remove the sale ranges as sometimes they happen without my knowledge. Excel enables you to add a trendline to a chart and also provides the.
Sounds easy, except for sales and promotions happening all-to-often, and they produce huge spikes in the data that I don't care about. ORDER BY units_sold thing I want to know is an "average" units/day for each store, so I can keep an eye on long-term trends. I spent the last few days writing a python script to take all the data and move it into a single SQL database so I could quickly query tons of useful untry, SUM(sales.units_sold) AS units_sold
#Excel trendline ignore outliers download
It's time-consuming and frustrating to download each sales report once a month and hand massage the data into a useable format. Thimbleweed Park is out on seven different digital stores and each of them has a completely different way of reporting sales. The number of data points is provided as a percentage.
TRIMMEAN works by first excluding values from the top and bottom of a data set, then calculating mean. The number of data points to exclude is provided as a percentage.
Much of my education is like this: "Why the hell would I ever need this", only to find out years later that I desperately needed it (yeah, I'm talking to you, high school algebra). The Excel TRIMMEAN function calculates mean (average) while excluding outliers. I took a statistics class in college, but I didn't pay much attention due to never seeing a need for the knowledge.