Hello,
Attached is an image of what my sheet looks like to be able to visually understand my problem. What I have done with my sheet so far is create a pivot table that filters for material type and pulls all the data concerned with that material. What I have done from there is in the statistics column on the right, I have determined what orders are considered outliers and early arrivals based on their DTM as compared to the Lead Time. Then I did my own calculation for new Lead Times using the Mean and Median functions of the data from the material to recommend a potential change to the lead time to have it be more reflective of the actual situation. Where I am stuck now and what I would like to do is in those empty boxes titled New Outlier Count and New Early Count have it read from column B since that is where the data of interest will always be, run a check that if the data point is 2.5 times greater than that of the new lead time it is considered an outlier, and then count up all occurrences of outliers for that material using that check for each data point. Similarly, the early count I would like to take each data point and see if it is less than the new lead time and then count up all occurrences of that. I've tried using a COUNTIF function but I kept getting #SPILL errors so I am unsure if I was using it correctly or not. What function or set of functions would be best to accomplish what I am looking for?
sheet.PNG
Bookmarks