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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:uX0Av1VZGHA.4752@TK2MSFTNGP02.phx.gbl...
> =INDIRECT("A"&34-ROW())-A4
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "mworth01" <mworth01.26mf8q_1145634023.1264@excelforum-nospam.com> wrote
in
> message news:mworth01.26mf8q_1145634023.1264@excelforum-nospam.com...
> >
> > 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
> >
>
>

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.

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" <mworth01.26mncb_1145644505.9703@excelforum-nospam.com> wrote in
message news:mworth01.26mncb_1145644505.9703@excelforum-nospam.com...
>
> 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
>

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.