Discontinuous range selection in Excel

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.

=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.


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.

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.

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.

=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.

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

What the heck is a “large” function?

Finds the nth largest number in a range.