• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula that calculates dynamic differences between consecutive numbers

Celia

New Member
I need help to determine how to calculate a moving range dynamically so that the

absolute number between two consecutive numbers in a column are determined. I need

a formula that can reference the cells without referring to their specific row #, so that the difference column will update automatically when a new number is added.


As shown below, the first cell for difference will be empty of course...then I wish

to insert a formula to calcuate the differences automatically upon entry of new month of data.


For example,

month Blood pressure Difference

jan 98

feb 99 1

march 96 3 etc.
 
Celia

Assuming your data starts in A1 in the cell C4 where you have 3 (99-96),

Wouldn't you just put

=B4-B3


or if you are only interested in the difference and don't care about +/-'s use

=Abs(B4-B3)


Copy either equation down as you enter new data
 
Thank-you ;)


Do you know if there is a way to create a formula that can create the

consecutive differences without having to change the formula each time.

It would be most desirable to use it as a named formula in a line chart that

could display the changes over time as the user entered new blood pressure values.


Any ideas would be immensely appreciated!
 
Hi Celia,


If you are using Excel 2007, you can convert your data into a table and this will automaitcally copy the formula from the cell above when you add a new record.
 
Celia

You don't have to change the formula, Excel will do that for you when you copy it.

As Kchiba mention you can convert the area to a Table, Insert Table and follow prompts

or

After you enter data into A5 & B5 move to C5 and press Ctrl D

Ctrl D, Duplicates the cell above and if a Formula copies it down relatively.
 
Back
Top