title: Excel Functions (Average, Standard Deviation, etc) with multiple nested IFS
body: <p>I am having difficulty calculating descriptive statistics based on multiple conditions in Excel.</p>
  <p>Here's a subset of the dataset:</p>
  <pre><code>A   B
  1   2   
  
  1   3
  3   1
  1   
  </code></pre>
  <p>The blanks indicate missing data - <strong>I cannot rename the blanks (e.g., -999 or NaN)</strong></p>
  <p>Let's say I want to calculate the average. I know that the true average <strong>for values in column A</strong> which have an entry in both columns A and B is 1.67 (based on filtering the data to rows which have entries in both columns).</p>
  <p>I have tried a series of functions to account for this condition (e.g., calculate the average of column A for values with an entry in both columns A and B) and the only function that works is <code>AVERAGEIFS</code>. However, I cannot rely on this because I want to use the code for any function, e.g., <code>STDEV</code>, for which an <code>FUNCTIONIFS</code> function does not (yet) exist.</p>
  <p>Here's what I've tried, with no luck:</p>
  <p><a href="https://i.stack.imgur.com/Xep1g.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Xep1g.png" alt="enter image description here" /></a></p>
  <p>Here's the code for ease of copying+pasting:</p>
  <pre><code>=AVERAGEIFS(A:A,A:A, &quot;&gt;=0&quot;,B:B,&quot;&gt;=0&quot;)
  =AVERAGE(IF(A:A &gt;=0,IF(B:B &gt;=0,A:A)))
  =AVERAGE(IF(AND(A:A &gt;=0,B:B &gt;=0),A:A))
  =IF(A:A &gt;=0,IF(B:B &gt;= 0,AVERAGE(A:A)))
  =IF(AND(A:A&gt;=0,B:B&gt;=0),AVERAGE(A:A))
  =AVERAGE(IF((A:A&gt;=0)*(B:B),A:A))
  </code></pre>
  <p>To recap, there are three conditions that are necessary:</p>
  <p><strong>1. I need to reference the whole column (e.g., A:A) rather than index a subset of it (e.g., A1:A50).</strong>
  2. I need to include missing values as blanks
  3. I cannot rely on AVERAGEIFS because I am looking to generalise the code to any function (e.g., STDEV)</p>
  <p>Any guidance would be greatly appreciated.</p>