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

andycap

New Member
Joined
Feb 24, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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:)
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
Does it return a value or an error?
 

andycap

New Member
Joined
Feb 24, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 24, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

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

ADVERTISEMENT

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

andycap

New Member
Joined
Feb 24, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
As that is a different problem, you need a new thread. Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,129,274
Messages
5,635,232
Members
416,848
Latest member
Excel DG

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