Title: How to combine two disjoint ranges in an array formula
Tags: excel
Question: How can I combine two disjoint ranges to be used in an array formula.
Example:
{=PERCENTILE(10^((E2:F2)/10),0.7)}
This works however:
{=PERCENTILE(10^((E2:F2,N2:P2)/10),0.7)}
This will fail.
How can I get this functionality to work in excel?
Comment: Yes I can do it with a continuous range but not a disjoint range.  I would like to know how I can achieve this with a disjoint range

Comment: How would I do a UDF.  I tried a union but that did not seem to work

Comment: worth doing a UDF for the whole first parameter expression maybe...

Comment: For other Googlers, seems like the [comma operator is the right answer when it works (which is not always unfortunately)](https://superuser.com/a/447560/10136), and unlike [Google sheets](https://stackoverflow.com/q/10793321/1026), Excel [doesn't allow](https://stackoverflow.com/q/46002409/1026) to construct arrays from cell references (i.e. `SUM({E2:F2,N2:P2})`)

Comment: It's seeming like your exponentiation is where this breaks down. You can do exponentiation in an array formula, but not with disjointed ranges.


Here is another answer: This is an old question, but since this is what comes up in Google searches for disjoint ranges:   newer excel versions support joining disjoint ranges using colon:
{=PERCENTILE(10^((E2:F2:N2:P2)/10),0.7)
You can add a colon and another start:end style range, as many times as you want.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 5, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2017:00:06/06/17', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<excel>', 'Title': 'How to combine two disjoint ranges in an array formula', 'ViewCount': 667, 'id': 44399348, 'user_AboutMe': '', 'user_AccountId': 1811996.0, 'user_DisplayName': 'eng3', 'user_Reputation': 369.0, 'user_Views': 78.0, 'user_id': 1647854.0}}