0

I have a data list in Excel, I am looking to take the top 3 values for each number, and get the average for those 3 values quickly. I often work with lists of up to 50,000 lines which at any one time could convert to over 10,000 different column A numbers.

I understand basic pivot tables to get an average after the top 3 values are collected, but need to find a way to remove all values that are not the top 3,

here is an example of a sheet I would be working with;

I trust this may be an extremely simple ask, or complex and thank you in advance for your help.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Josh Booth
  • 1

1 Answers1

0

you can use =LARGE(Array, k) formula. For example, =LARGE(B:B, 1) is for 1-largest number, =LARGE(B:B, 2) is for 2-largest number etc.

If column contains many duplicates, and you want to get all occurences of top three values, use this formula to get all of them (put:

=IF(LARGE(B:B,ROW(A1))>=LARGE(B:B,COUNTIF(B:B,LARGE(B:B,COUNTIF(B:B,MAX(B:B))+1))+COUNTIF(B:B,MAX(B:B))+1),LARGE(B:B,ROW(A1)),"")
bzimor
  • 1,618
  • 2
  • 14
  • 26
  • Here you can check my example excel workbook: https://drive.google.com/open?id=0BzFv0oeets6uZ3NjaTlUa1l2c1k – bzimor Oct 28 '16 at 11:53
  • Hi bzimor, thank you for your response, I may be looking at this wrong, and apologies if I am, but I don't think that is quite what I am trying to do. I require the top 3 values for each number/letter. For example, in your workbook, if there were 5 'a' values with 100,90,80,70,60 and 50. I need the formula to either highlight 100,90 and 80, or remove the rest so I quickly see the top 3 for each individual account, I understand that is relatively easy if all I have is single letter, but when the accounts are so varied is where I am having the issue. – Josh Booth Oct 31 '16 at 08:58
  • after thought, if each individual reference in column 'a' could be numbered starting from 1-X depending on how many values there are, i could then just sort z-a by value and filter for 1,2&3. maybe this would be an easier way of doing things? – Josh Booth Oct 31 '16 at 09:36
  • I think I may have worked the simpler way out, =IF(B3=B2,A2+1,1) so basically each new line checks the one before for a value that has been before, if it exists the numbers increase by 1, if they don't it starts again at 1. thanks for your help anyway, I just think I was trying to do too much when there was a much simpler way. – Josh Booth Oct 31 '16 at 09:48