Formula to get the average of the top 3 numbers in an array

sobrien1234

Board Regular
Joined
May 10, 2016
Messages
162
Office Version
  1. 365
Platform
  1. Windows
in column A I have a list of names with up to 8 of the same name. In column B I have numbers which represent times. I want a formula in column that will extract the average of the top 3 numbers for each name.

For example the answers to the formula in column C would be....

NAMETIMEAVERAGE OF LOWEST 3 TIMES
MEPUNGA DANIEL-0.05-0.30
MEPUNGA DANIEL-0.64-0.30
MEPUNGA DANIEL-0.05-0.30
MEPUNGA DANIEL-0.19-0.30
MEPUNGA DANIEL0.12-0.30
MEPUNGA DANIEL0.41-0.30
IKARUGA BALE-0.22-0.11
IKARUGA BALE-0.02-0.11
IKARUGA BALE0.02-0.11
IKARUGA BALE-0.03-0.11
IKARUGA BALE-0.07-0.11
IKARUGA BALE0.40-0.11
IKARUGA BALE0.53-0.11
IKARUGA BALE0.16-0.11
IKARUGA BALE0.00-0.11
SLINGSHOT TITAN-0.24-0.28
SLINGSHOT TITAN-0.55-0.28
SLINGSHOT TITAN0.13-0.28
SLINGSHOT TITAN0.06-0.28
SLINGSHOT TITAN-0.06-0.28
SLINGSHOT TITAN0.06-0.28
SLINGSHOT TITAN-0.06-0.28
SLINGSHOT TITAN0.17-0.28
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,245
Office Version
  1. 365
Platform
  1. Windows
Try this:

=AVERAGE(SMALL(IF($A$2:$A$24=A2,$B$2:$B$24),{1,2,3}))
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
You could also employ one of Excel 365's new functions.

20 08 20.xlsm
ABC
1NAMETIMEAVERAGE OF LOWEST 3 TIMES
2MEPUNGA DANIEL-0.05-0.29
3MEPUNGA DANIEL-0.64-0.29
4MEPUNGA DANIEL-0.05-0.29
5MEPUNGA DANIEL-0.19-0.29
6MEPUNGA DANIEL0.12-0.29
7MEPUNGA DANIEL0.41-0.29
8IKARUGA BALE-0.22-0.11
9IKARUGA BALE-0.02-0.11
10IKARUGA BALE0.02-0.11
11IKARUGA BALE-0.03-0.11
12IKARUGA BALE-0.07-0.11
13IKARUGA BALE0.4-0.11
14IKARUGA BALE0.53-0.11
15IKARUGA BALE0.16-0.11
16IKARUGA BALE0-0.11
17SLINGSHOT TITAN-0.24-0.28
18SLINGSHOT TITAN-0.55-0.28
19SLINGSHOT TITAN0.13-0.28
20SLINGSHOT TITAN0.06-0.28
21SLINGSHOT TITAN-0.06-0.28
22SLINGSHOT TITAN0.06-0.28
23SLINGSHOT TITAN-0.06-0.28
24SLINGSHOT TITAN0.17-0.28
Average best 3
Cell Formulas
RangeFormula
C2:C24C2=AVERAGE(SMALL(FILTER(B$2:B$24,A$2:A$24=A2),{1,2,3}))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,484
Messages
5,596,407
Members
414,064
Latest member
Duncthegreat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top