Trying to Average / Large on 2 ranges on same row

andycap

New Member
Joined
Feb 24, 2021
Messages
15
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})
 
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! :eek:)
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Does it return a value or an error?
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
As you now have just one range you can use
Excel Formula:
=AVERAGE(LARGE(H8:AA8,SEQUENCE(8)))
 
Upvote 0
How about
Excel Formula:
=AVERAGE(LARGE(H8:AA8,SEQUENCE(MIN(8,COUNTIFS(H8:AA8,">0")))))
 
Upvote 0
Solution
How about
Excel Formula:
=AVERAGE(LARGE(H8:AA8,SEQUENCE(MIN(8,COUNTIFS(H8:AA8,">0")))))
That worked like a champ!! (y) 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?
 
Upvote 0
As that is a different problem, you need a new thread. Thanks
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

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
Back
Top