title: Array function on a logic-tested OFFSET
body: <p>I have a simple function in Excel.</p>
  
  <p>In column A, I have some numbers. In column B, I have a function in the form (for B6): <code>{=MAX(ROW(A$1:A5)*(A$1:A5&lt;A6))}</code></p>
  
  <p>This is all well and good. It finds the nearest preceding row with a value less than the current row's. But if I insert a row, one of the functions will get messed up, simply because there are relative references.</p>
  
  <p>I have tried converting the <code>A$1:A5</code>'s to <code>OFFSET</code>s, but for some reason, that doesn't work:</p>
  
  <p><code>{=MAX(ROW(OFFSET(A$1,0,0,ROW()-1))*(OFFSET(A$1,0,0,ROW()-1)&lt;A6))}</code></p>
  
  <p>Having broken it down with some testing, it's clearly the second instance of <code>OFFSET</code>, and the associated logical test, that is causing the problem. But I can't figure out why. Or how to fix it.</p>
  
  <p>Is there a way of getting this to work, or an alternate way of performing this query that will be insert-safe?</p>