# Discontinuous range selection in Excel

**URL:** <https://boards.straightdope.com/t/discontinuous-range-selection-in-excel/627347>\
**Category:** Factual Questions\
**Created:** [July 5, 2012, 8:04pm UTC](https://boards.straightdope.com/t/discontinuous-range-selection-in-excel/627347 "2012-07-05T20:04:09Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\
**Post date:** [July 5, 2012, 8:04pm UTC](https://boards.straightdope.com/t/discontinuous-range-selection-in-excel/627347/1 "2012-07-05T20:04:09Z")

</div>

I want to average up a6:f6 AND a10, all in one formula. How do I do this?

I figured =average(a6:f6; a10) should work, but apparently not.

---

<div class="post-metadata">

**Author:** ![md2000](https://avatars.discourse-cdn.com/v4/letter/m/73ab20/32.png) [@md2000](https://boards.straightdope.com/u/md2000)\
**Post date:** [July 5, 2012, 8:05pm UTC](https://boards.straightdope.com/t/discontinuous-range-selection-in-excel/627347/2 "2012-07-05T20:05:19Z")

</div>

=average(a6:f6 \*\*\*, \*\*\*a10) Maybe?

---

<div class="post-metadata">

**Author:** ![Leaffan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/leaffan/32/299_2.png) [@Leaffan](https://boards.straightdope.com/u/Leaffan)\
**Post date:** [July 5, 2012, 8:10pm UTC](https://boards.straightdope.com/t/discontinuous-range-selection-in-excel/627347/3 "2012-07-05T20:10:32Z")

</div>

> [@md2000](#):
>
> =average(a6:f6 \*\*\*, \*\*\*a10) Maybe?

Yeah, the comma is the key; however you can select the cells by doing the column first, and then holding the control key while selecting any remaining cells.

---

<div class="post-metadata">

**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\
**Post date:** [July 5, 2012, 8:11pm UTC](https://boards.straightdope.com/t/discontinuous-range-selection-in-excel/627347/4 "2012-07-05T20:11:19Z")

</div>

Thanks!

For some reason I have a strong disinclination to use manual selection in excel. So I’m glad to know how to type it in.

---

<div class="post-metadata">

**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\
**Post date:** [July 5, 2012, 8:25pm UTC](https://boards.straightdope.com/t/discontinuous-range-selection-in-excel/627347/5 "2012-07-05T20:25:27Z")

</div>

Didn’t work, but it’s my fault because I unintentionally misdescribed the problem.

What I’m actually doing is using the large function, not the average function. (I said average in the OP because the large function is embedded inside an average function and I forgot, while typing the OP, that it was the “large” function that was giving me trouble and not hte “average” one.)

Here’s what doesn’t work:

=average(large(c6:l6; n6, {1, 2, 3, 4, 5, 6}))

That yields a syntax error.

But replacing the semicolon with a comma also yields a syntax error, presumably because it wants the comma to mean “we’re finished defining the range, now let’s define the degree of largeness we’re looking for.”

What I want is for the largest 6 of the eleven numbers to be averaged up, but the numbers must remain discontinuous due to things happening in other rows.

Any ideas?

ETA: Manual selection tries to use a comma just as you guys suggested above, but nevertheless this results in a syntax error.

---

<div class="post-metadata">

**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\
**Post date:** [July 5, 2012, 8:33pm UTC](https://boards.straightdope.com/t/discontinuous-range-selection-in-excel/627347/6 "2012-07-05T20:33:36Z")

</div>

I’m beginning to think there will not be a way to do this that doesn’t constitute an incredibly complicated workaround using a series of “max” and “average” functions.

---

<div class="post-metadata">

**Author:** ![mcgato](https://avatars.discourse-cdn.com/v4/letter/m/ac8455/32.png) [@mcgato](https://boards.straightdope.com/u/mcgato)\
**Post date:** [July 5, 2012, 8:34pm UTC](https://boards.straightdope.com/t/discontinuous-range-selection-in-excel/627347/7 "2012-07-05T20:34:27Z")

</div>

Try:  
=average(large({c6:l6, n6}, 1),large({c6:l6, n6}, 2),large({c6:l6, n6}, 3),large({c6:l6, n6}, 4),large({c6:l6, n6}, 5),large({c6:l6, n6}, 6))

I don’t have Excel on my home computer, so I can’t check it.

---

<div class="post-metadata">

**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\
**Post date:** [July 5, 2012, 9:04pm UTC](https://boards.straightdope.com/t/discontinuous-range-selection-in-excel/627347/8 "2012-07-05T21:04:34Z")

</div>

Okay, turns out I just needed to put parentheses around the cell range, like so:

=average(large((c6:l6,n6), {1, 2, 3, 4, 5, 6}))

---

<div class="post-metadata">

**Author:** ![Leaffan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/leaffan/32/299_2.png) [@Leaffan](https://boards.straightdope.com/u/Leaffan)\
**Post date:** [July 6, 2012, 12:35am UTC](https://boards.straightdope.com/t/discontinuous-range-selection-in-excel/627347/9 "2012-07-06T00:35:55Z")

</div>

What the heck is a “large” function?

---

<div class="post-metadata">

**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\
**Post date:** [July 6, 2012, 1:36am UTC](https://boards.straightdope.com/t/discontinuous-range-selection-in-excel/627347/10 "2012-07-06T01:36:03Z")

</div>

> [@Leaffan](#):
>
> What the heck is a “large” function?

Finds the _n_th largest number in a range.