title: Excel formula: Union multiple ranges into 1 function argument
body: <p>I have four dynamic ranges called <code>ADate</code>, <code>ALeadTime</code>, <code>BDate</code>,  and <code>BLeadTime</code> (two pairs) and a cell called <code>MyDate</code>.</p>
  
  <p>Using array formulas I can easily find the mean of <code>LeadTime</code> based on the union of two different ranges within a given week by something like</p>
  
  <pre><code>{=AVERAGE(
      IF((ADate&gt;=MyDate)*(ADate&lt;MyDate+7),ALeadTime),
      IF((BDate&gt;=MyDate)*(BDate&lt;MyDate+7),BLeadTime))
  }
  </code></pre>
  
  <p>This works well because <code>AVERAGE</code> accepts a variable number of arguments. However, if I want to use a different function such as <code>PERCENTILE</code> which only accepts a single argument this does not work.</p>
  
  <p>How can I union the two ranges into one? I am aware of VBA's <code>Application.Union</code> but no VBA thanks. I have been messing around with something like</p>
  
  <pre><code>{=PERCENTILE(CHOOSE({1,2},
      IF((ADate&gt;=MyDate)*(ADate&lt;MyDate+7),ALeadTime)), 
      IF((BDate&gt;=MyDate)*(BDate&lt;MyDate+7),BLeadTime))), 0.8)
  }
  </code></pre>
  
  <p>without any luck.</p>