Trying to Average / Large on 2 ranges on same row

[andycap]

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})

 

[kweaver]

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)

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]

You could also do this:

=AVERAGE(LARGE(G1:AC1,{1,2,3,4,5,6,7,8}))

 

[andycap]

You could also do this:

=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]

How about

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

 

[andycap]

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.

 

[andycap]

How about

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

I had hopes but this code didn't work.

 

[Fluff]

In what way?

 

[andycap]

In what way?

When I added or changed a number in either range the quota in cell S62 didn't change.

 

[Fluff]

Do you have calculations set to automatic? Check the "Calculations Option" on the formula tab