I need help with an Excel 2010 worksheet. I have calculated the n-Percentile of a data series. Now I need to find a way (preferably a formula) that can calculate an average on the values of the data series but only if they fall within some of the Percentiles.
Since I have many data series (thousands), I need something that works automatically. The problem with the Averageif function is that the criteria cannot be referenced to cells. This means that I should enter the Percentiles as criteria in the averageif function for every data series. It will take too much time and result in a mess.
Any workaround or solution ? MUST BE EXCEL..and very simple too.
Thanks See sample file, please