I have a simple question for which I have failed to find an answer, simple or otherwise!
My Spreadsheet looks like this (say)
A B C
1 Name Amount Tax
2 Neil 20 2
3 Rose 100 10
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.
I cannot find a way to do this automatically. I know how to
arrayformula(B2:B/10)
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 arrayformula(B3:B/10)
which is logical but not what I want because the new row 2 has no formula associated with itarrayformula(B:B/10)
then it applies to all rows including row 1 (the column headers) and gets very confused about position.There must be a way, so I ask you, the internet for your assistance :-)
An ARRAYFORMULA
actually will work in this situation if you place it in cell C1 as follows:
=ARRAYFORMULA(IF(ROW(B:B)=1, "Tax", IF(ISBLANK(B:B), "", B:B/10)))
B:B/10
.If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With