title: Use QUERY to get Avg of last x entries matching conditions
body: <p>I'm trying to refine my coding for a working spreadsheet by updating all the code to <code>QUERY</code> functions.  One thing I am stuck on is where I average the last x rows matching specific conditions.</p>
  
  <p>I was hoping that the following formula would work:</p>
  
  <pre><code>=QUERY(Ladder!A2:AE,"Select Avg(J) where F = '"&amp;H$1&amp;"' order by B desc limit "&amp;$C$5)
  </code></pre>
  
  <p>My thought being that this would return the average of the last 3 rows - but I receive the </p>
  
  <blockquote>
    <p>COL_IN_ORDER_MUST_BE_IN_SELECT error.</p>
  </blockquote>
  
  <p>I only want the formula to return a single cell with the % average in it.</p>
  
  <p>I realise that I could use the following code and then <code>QUERY</code> the Avg of that, but I want to be able to return % values for each different deck.</p>
  
  <p>Here's a link to a cut down version of the spreadsheet:</p>
  
  <p><a href="https://docs.google.com/spreadsheets/d/1Bt5wXrBrM7Qrp5XAAiAALbFwmpUxDXqZyuYElhMmb0E/edit?usp=sharing" rel="nofollow noreferrer">Spreadsheet</a></p>
  
  <p>Ladder:  Where I enter the data
  Ladder Filter:  Where the data will filter based on query commands depending on criteria in the Ladder Stats tab
  Ladder Stats:  Where I can view all statistics and generate various statistical reports</p>
  
  <p>It's all working at the moment, but as you can see it's very clunky and formula-heavy.  I've worked most of it out except for the scenario you will find on the "Ladder Stats" tab - whereby B3="" and C5 != NULL.  It is designed to give statistics for the last x games played by each of the decks.</p>