0

I have a data set of simulations where I am calculating the total daily sales of 5000 different combinations of a group of 6 sales people, based off the individual sales members' average daily sales total and std. I have already ran the simulation 10000 times and have a 5000x10000 array of all the trials. Each trial is independent of the other 9999 trials. Row 1 has the group ID numbers 1-5000, column A has the trials numbered 1-10000.

What I am trying to do, with no luck, is find out how many times out of the 10000 trials, each of the 5000 groups was in the top 10% of total sales.

I have minimal coding experience outside of normal excel formulas. Is there an easy way to do this with normal formals or simple VBA? I was hoping for something like a COUNTIF the number in this column is greater than the 90%percentile of the row it is in, and then be able to carry it through all 5000 groups.

**Edit 1: to clarify, what I'm hoping to find is a formula that would count if the number in each cell in this column is in the top 10% of all the numbers in its respective row, or vice versa. I can copy and paste transposed if the formula needed to be the number in each cell in this row is in the top 10% of the numbers in it's respective column.

AntB
  • 1
  • 1
  • 1
    Look at the `SMALL` and `LARGE` worksheet functions. One route is to use them to find the 90th percentile value. – Mark Balhoff Aug 23 '21 at 16:32
  • Is your data in Excel? Why not just have one cell specify what 10% and then create a calculation for each result with a `true/false` result if it's higher or lower? – pgSystemTester Aug 23 '21 at 17:41
  • There are a couple of q&a about top 5 or top 10 on here - worth having a look. – Solar Mike Aug 23 '21 at 17:53
  • See https://stackoverflow.com/a/56016036/4961700 – Solar Mike Aug 23 '21 at 17:55
  • @pgSystemTester the data is in excel but each trial is independent of the next so I would have to specify what 10% is of each of the 10,000 trials, and then count if True/False for each of the 5000 groups over the 10000 top 10%s. I'm not sure if there is a way to do that without creating a whole new 5k x 10k array? – AntB Aug 23 '21 at 18:41
  • @SolarMike I looked at the other posts but they all return the top X values in which case I would still need a way to count them... I suppose in that way I could Index/Match value with it's group id number then count if group id number but I think with the amount of data it might kill Excel. – AntB Aug 23 '21 at 18:46
  • Those top X values in my answer ARE the counts for each... – Solar Mike Aug 23 '21 at 18:57

0 Answers0