Google Sheets: automatically apply formula to new rows added at the top
<p>I have a simple question for which I have failed to find an answer, simple or otherwise!</p>
<p>My Spreadsheet looks like this (say)</p>
<pre><code>   A       B      C
1  Name  Amount  Tax 
2  Neil    20      2
3  Rose    100     10
</code></pre>
<p>Now column C is calculated via a formula B/10. I want to insert a new row
ABOVE ROW 2 (not at the end) and I want the formula to apply to that new row in column C.</p>
<p>I cannot find a way to do this automatically. I know how to</p>
<ul>
<li>manually copy the formula (but of course there are many columns I actually want to do this to) by copy and paste or grabbing the handle</li>
<li>use arrayformula to extend the formula.  The problem I have is that
if I use arrayformula like this: <code>arrayformula(B2:B/10)</code> that's great if I add rows at the end.  But when I insert a row, becoming the new row 2, what happens is that the formula remains linked with the original cell I entered and changes to <code>arrayformula(B3:B/10)</code>  which is logical but not what I want because the new row 2 has no formula associated with it</li>
<li>if I use <code>arrayformula(B:B/10)</code> then it applies to all rows including row 1 (the column headers) and gets very confused about position.</li>
</ul>
<p>There must be a way, so I ask you, the internet for your assistance :-)</p>