# Trying to Average / Large on 2 ranges on same row

#### andycap

##### New Member
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
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
You could also do this:
Code:
``=AVERAGE(LARGE(G1:AC1,{1,2,3,4,5,6,7,8}))``

#### andycap

##### New Member
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

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

#### andycap

##### New Member
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

##### New Member

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

In what way?

#### andycap

##### New Member
In what way?
When I added or changed a number in either range the quota in cell S62 didn't change.

#### Fluff

##### MrExcel MVP, Moderator
Do you have calculations set to automatic? Check the "Calculations Option" on the formula tab

Replies
13
Views
74
Replies
3
Views
70
Replies
0
Views
36
Replies
6
Views
138
Replies
4
Views
239

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.

### Which adblocker are you using?

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

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