Copying formula with cell reference decreasing automatically

[mworth01]

I have one column with numbers ranging in cells from A4 to A30. I want
to create a formula in B4 and then copy the formula down so that the
formula adjusts automatically so that the following results:

Cell Formula
B4 =A30-A4
B5 =A29-A5
B6 =A28-A6
B7 =A27-A7
etc.

My problem is if I create a formula and copy down, the A4 to A5 part
works but A30 wants to become A31 instead of A29. I then tried a
series of equations using INDEX and ROW, but can't seem to find the
right combonations that Excel will allow. This seems like it should be
relatively simple but I'm stumped. Any advice? Thanks in advance.

 

[Bob Phillips]

=INDIRECT("A"&34-ROW())-A4

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Hans Knudsen]

The following worked for me.
In B5: ="=A"&30-ROW(A1)&"-"&"A"&ROW(A5)
Copy down to B30. Now take a copy of B5:B30 and paste as values to (for example) C5:C30. Highligt C5:C30, press F2, hold down Ctrl
while pressing Enter. Now you should have the formulas in C5:C30. Cut and paste back to B5:B30 if you want.

Hans

 

[Hans Knudsen]

Pardon me. Doesn't work.
Hans

The following worked for me.
In B5: ="=A"&30-ROW(A1)&"-"&"A"&ROW(A5)
Copy down to B30. Now take a copy of B5:B30 and paste as values to (for example) C5:C30. Highligt C5:C30, press F2, hold down Ctrl
while pressing Enter. Now you should have the formulas in C5:C30. Cut and paste back to B5:B30 if you want.

Hans

 

[Gary''s Student]

First enter:

=INDIRECT(ADDRESS(34-ROW(),1))-A4
in B4 and copy down

Then read Excel Help on both ADDRESS() and INDIRECT(). They are both really
neat!

 

[Bob Phillips]

Actually, this is better

=INDEX(A:A,34-ROW())-A4

no INDIRECT

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

=INDIRECT("A"&34-ROW())-A4

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

message news:[email protected]...

 

[mworth01]

Thanks for all of the replies so far. Unfortunately, I haven't been
able to get any of them to work yet. Let me try a smaller example,
starting in A4. Column B is what the results should look like (30-5,
25-10, 20-15):

(Ignore the underlines...they are just there for formatting purposes)
Col. A____Col. B
5________25
10_______15
15_______5
20
25
30

Your ideas definitely introduced me to some new formulas, so I'm going
to try to modify your equations and see if I can't figure it out. I
may not have explained myself properly in my first post, so hopefully
having numbers will make it more clear. Again, the key is that I want
to be able to use the fill down feature to copy the formula in column B
for all of my data (I have over 2000 points that I need to apply this
formula to). In the equations that were suggested, the 34 in
=INDIRECT("A"&34-ROW())-A4 doesn't change so I'm always subtracting the
new row (A4, A5, etc.) from the data in A34. I need A34 to become A33,
A32, etc. Again, hopefully the numbers speak to where my words are
failing. Thanks so much for looking at this.

 

[Bob Phillips]

The example that you are giving now is nothing like the previous, and we all
worked to that. Originally you said =A30-A4, now you say =A5-A1. What
exactly do you want? Is it static, variable, what?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[mworth01]

It is supposed to be the same. In my first example, the equation for B
that I listed was A30-A4 (the very last row minus the very first row).
In B5 the equation was A29-A5 (the second to last row minus the secon
row) and so on until the two ends of data merge. The only differenc
between my first and second examples is that I changed my data rang
from A4 to A30 to A4 to A9 so that I didn't have to type nearly as man
numbers (or call it A1 to A6 if you like - I've just given example
before that start in the first row and someone responds with a formul
that works for that case, but won't if the data starts in any othe
row; my solution below requires an additional row above the startin
row of data). The range of data is fixed. The key is that the formul
in each consecutive row has both ends of the data range converging on
cell closer to the other. My problem was finding a way to get the dat
at the bottom of the range to step backwards towards the top.

Since everyone's examples were giving me the same result, I realiz
that I didn't explain it very well the first time - although I though
listing the equations for each row in column B would have made i
clear.

I was able to figure it out though, thanks to your attempts. If yo
plug in my numerical example in A4 to A9 and then enter the followin
into B4, you can copy the equation down to B6 and the solution is wha
I wanted:

=INDIRECT(ADDRESS(ROW($A$4)+ROW($A$9)-ROW(*A4*),2))-INDIRECT(ADDRESS(ROW(*A3*)+1,2)).

The absolute ($) versus relative (in bold) cell references is key.
Sorry for the confusion