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

#### andycap

##### New Member
Do you have calculations set to automatic? Check the "Calculations Option" on the formula tab
Yes, calculations are set to automatic. I do appreciate you putting effort into this! )

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Fluff

##### MrExcel MVP, Moderator
Does it return a value or an error?

#### andycap

##### New Member
Yes, calculations are set to automatic. UPDATE: I've re-layed out the spreadsheet where the range is continuous (one range of 20 values) and I have the formula working but it counts blank cells and zeros. I can't figure out how to make the formula not count the blanks and zeros. I've tried using AVERAGEIF function with the ("<>0") but with the LARGE function I can't make it work. Without LARGE function it works as advertised. Furthermore, some players may not have 8 scores yet so I need it to calculate an average of their scores, up to 8 highest values out of 20. In other words, a player just started playing with our league and is establishing their average quota (we use a points system). For new players we fix their quota for the first 3 rounds. We don't start averaging their scores until they play their 4th round. I appreciate your efforts in helping to figure this out.

#### andycap

##### New Member
 I used your previous formula and altered the range to the continuous range. I also am using a different row # than previously. =AVERAGE(LARGE(CHOOSE({1;2},\$H8:\$AA8),SEQUENCE(8))) I get this error. #VALUE!

#### Fluff

##### MrExcel MVP, Moderator

As you now have just one range you can use
Excel Formula:
``=AVERAGE(LARGE(H8:AA8,SEQUENCE(8)))``

#### andycap

##### New Member
As you now have just one range you can use
Excel Formula:
``=AVERAGE(LARGE(H8:AA8,SEQUENCE(8)))``
If there is less than 8 numbers then it returns #NUM.

#### Fluff

##### MrExcel MVP, Moderator

Excel Formula:
``=AVERAGE(LARGE(H8:AA8,SEQUENCE(MIN(8,COUNTIFS(H8:AA8,">0")))))``

#### andycap

##### New Member
Excel Formula:
``=AVERAGE(LARGE(H8:AA8,SEQUENCE(MIN(8,COUNTIFS(H8:AA8,">0")))))``
That worked like a champ!! Big problem solved! Thank you, thank you! I have another problem dealing with Conditional Formatting if you are able to help. I'd like the players to know which of their 8 scores are being averaged out of the 20. Using the Top Rank of 8 it works but if there are duplicate scores then it can highlight 9 or more scores. This would be confusing to the players. Is there a solution with a formula in Conditional Formatting?

#### Fluff

##### MrExcel MVP, Moderator
As that is a different problem, you need a new thread. Thanks

Replies
13
Views
93
Replies
3
Views
71
Replies
0
Views
40
Replies
6
Views
145
Replies
4
Views
246

1,130,193
Messages
5,640,771
Members
417,166
Latest member
Funwayo

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