Trying to Average / Large on 2 ranges on same row

andycap

New Member
Joined
Feb 24, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I have 2 ranges with 10 values each for a total of 20 values. I need to find the 8 highest values out of the 20 and average them. I get it to work using only one range but as soon as I add the second range (the way I understand how to) I get a #NUM! error stating that "There is a problem with a number in the formula". Furthermore, I add values to these ranges almost daily via VBA command button so I'm not apposed to a VBA resolution when I update numbers. But I'm fine using the formula directly in the result cell. BTW, the result cell (S62) is in between the 2 ranges.

Here is the formula I'm trying to use:

=AVERAGE(LARGE($G62:$P62,$T62:$AC62),{1,2,3,4,5,6,7,8})
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,732
Office Version
  1. 365
  2. 2010
Welcome to Mr. Excel.

There must be a formula like yours that will work. I tried a UDF as follows and it seems to work. I did: =AvgTop(G1:AC1,8)

Code:
Function AvgTop(rng1 As Variant, num As Long) As Variant
Dim L1 As Double, j As Long
L1 = 0
  For j = 1 To num
  L1 = L1 + WorksheetFunction.Large(rng1, j)
  Next j
AvgTop = L1 / num
End Function
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,732
Office Version
  1. 365
  2. 2010
You could also do this:
Code:
=AVERAGE(LARGE(G1:AC1,{1,2,3,4,5,6,7,8}))
 

andycap

New Member
Joined
Feb 24, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
You could also do this:
Code:
=AVERAGE(LARGE(G1:AC1,{1,2,3,4,5,6,7,8}))
This one works but I have 2 ranges and it's when I add the second range that I get the error.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
Excel Formula:
=AVERAGE(LARGE(CHOOSE({1;2},G62:P62,T62:AC62),SEQUENCE(8)))
 

andycap

New Member
Joined
Feb 24, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Here is a snapshot of my spreadsheet. Row 62 (Test 1) is my test player. Normally, we average the last 10 rounds for a players quota, we've been doing it this way for years. But, we're looking at stabilizing the quotas by averaging the best 8 rounds out of 20 so I added the range, 11-20, to the right so not to interfere with our current system that players can see. I have it programmed to move round 10 to cell T62 and so forth so that it populates the range out to 20 rounds. If the league approves averaging 8 out of 20 rounds then I will put all 20 rounds in one range.

Don't mean to bore anyone with the details of my goal but I just want to make it easier to visualize my objective.
Feb 24, 2021 Excel Average-Large.png
 

andycap

New Member
Joined
Feb 24, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
Excel Formula:
=AVERAGE(LARGE(CHOOSE({1;2},G62:P62,T62:AC62),SEQUENCE(8)))
I had hopes but this code didn't work.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
In what way?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Do you have calculations set to automatic? Check the "Calculations Option" on the formula tab
 

Watch MrExcel Video

Forum statistics

Threads
1,129,475
Messages
5,636,546
Members
416,923
Latest member
jarri

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