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