Need help with a formula

[Guest]

This is probably a simple question, but I need help as I'm not very
proficient with spreadsheets.

In my spreadsheet, the last two rows are calculations. One shows the
difference between the last two row of data and the one immediately before it
(e.g., B26-B27), and the other shows the difference between the last row of
data and the first (B$1-B27). This works fine, except that I am constantly
adding new "last" rows of data. This means I have to keep updating the
calculation rows (e.g., changing B$3-B27 to B$1-B28 and B26-B27 to B27-B28).
I would like Excel to do this automatically as each new row is added, but I
don't know how.

Any help would be appreciated. Thanks.

 

[Guest]

Look at the OFFSET() function.

Simple example. Last row with data to work with is row 27, and you have
formula in row 28:
=B$1-B27
if you change that to
=B$1-OFFSET(B28,-1,0)
the result will always be the value in the row above the cell with the
formula minus contents of B$1. So if you insert a new row at row 28, that
formula gets pushed down and Excel would automatically change it to:
=B$1-OFFSET(B29,-1,0)

For the =B26 - B27 type setup, assuming you want to calculate it in Row 28:
=OFFSET(B28,-2,0)-OFFSET(B28,-1,0)

Hope this make life a little easier for you.

 

[Guest]

Thanks. It worked perfectly.

Look at the OFFSET() function.

Simple example. Last row with data to work with is row 27, and you have
formula in row 28:
=B$1-B27
if you change that to
=B$1-OFFSET(B28,-1,0)
the result will always be the value in the row above the cell with the
formula minus contents of B$1. So if you insert a new row at row 28, that
formula gets pushed down and Excel would automatically change it to:
=B$1-OFFSET(B29,-1,0)

For the =B26 - B27 type setup, assuming you want to calculate it in Row 28:
=OFFSET(B28,-2,0)-OFFSET(B28,-1,0)

Hope this make life a little easier for you.

 

[Guest]

Glad I could help and thanks for letting us know it did fix things up for you.