Copying formula with cell reference decreasing automatically
 

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.


--
mworth01
------------------------------------------------------------------------
mworth01's Profile: http://www.excelforum.com/member.php...fo&userid=8991
View this thread: http://www.excelforum.com/showthread...hreadid=535004

Copying formula with cell reference decreasing automatically
 

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mworth01" wrote in
message ...

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.


--
mworth01
------------------------------------------------------------------------
mworth01's Profile:
http://www.excelforum.com/member.php...fo&userid=8991
View this thread: http://www.excelforum.com/showthread...hreadid=535004

Copying formula with cell reference decreasing automatically
 

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


"mworth01" skrev i en meddelelse
...

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.


--
mworth01
------------------------------------------------------------------------
mworth01's Profile: http://www.excelforum.com/member.php...fo&userid=8991
View this thread: http://www.excelforum.com/showthread...hreadid=535004

Copying formula with cell reference decreasing automatically
 

Pardon me. Doesn't work.
Hans

"Hans Knudsen" skrev i en meddelelse ...
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


"mworth01" skrev i en meddelelse
...

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.


--
mworth01
------------------------------------------------------------------------
mworth01's Profile: http://www.excelforum.com/member.php...fo&userid=8991
View this thread: http://www.excelforum.com/showthread...hreadid=535004

Copying formula with cell reference decreasing automatically
 

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!
--
Gary's Student


"mworth01" wrote:


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.


--
mworth01
------------------------------------------------------------------------
mworth01's Profile: http://www.excelforum.com/member.php...fo&userid=8991
View this thread: http://www.excelforum.com/showthread...hreadid=535004

Copying formula with cell reference decreasing automatically
 

Actually, this is better

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

no INDIRECT

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" wrote in message
...
=INDIRECT("A"&34-ROW())-A4

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mworth01" wrote
in
message ...

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.


--
mworth01
------------------------------------------------------------------------
mworth01's Profile:
http://www.excelforum.com/member.php...fo&userid=8991
View this thread:
http://www.excelforum.com/showthread...hreadid=535004

Copying formula with cell reference decreasing automatically
 

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.


--
mworth01
------------------------------------------------------------------------
mworth01's Profile: http://www.excelforum.com/member.php...fo&userid=8991
View this thread: http://www.excelforum.com/showthread...hreadid=535004

Copying formula with cell reference decreasing automatically
 

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" wrote in
message ...

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.


--
mworth01
------------------------------------------------------------------------
mworth01's Profile:
http://www.excelforum.com/member.php...fo&userid=8991
View this thread: http://www.excelforum.com/showthread...hreadid=535004

Copying formula with cell reference decreasing automatically
 

It is supposed to be the same. In my first example, the equation for B4
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 second
row) and so on until the two ends of data merge. The only difference
between my first and second examples is that I changed my data range
from A4 to A30 to A4 to A9 so that I didn't have to type nearly as many
numbers (or call it A1 to A6 if you like - I've just given examples
before that start in the first row and someone responds with a formula
that works for that case, but won't if the data starts in any other
row; my solution below requires an additional row above the starting
row of data). The range of data is fixed. The key is that the formula
in each consecutive row has both ends of the data range converging one
cell closer to the other. My problem was finding a way to get the data
at the bottom of the range to step backwards towards the top.

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

I was able to figure it out though, thanks to your attempts. If you
plug in my numerical example in A4 to A9 and then enter the following
into B4, you can copy the equation down to B6 and the solution is what
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.


--
mworth01
------------------------------------------------------------------------
mworth01's Profile: http://www.excelforum.com/member.php...fo&userid=8991
View this thread: http://www.excelforum.com/showthread...hreadid=535004