Need help with a formula

R

RCUSWALK

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.
 
J

JLatham

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.
 
R

RCUSWALK

Thanks. It worked perfectly.

JLatham said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top