+ Reply to Thread
Results 1 to 3 of 3

Substracting Previous Row from Current Row

  1. #1
    Registered User
    Join Date
    09-30-2007
    Posts
    10

    Substracting Previous Row from Current Row

    Hi,

    I have a list of numbers as below

    3
    3
    4
    5
    5
    8

    What i need is, i need a new column which contains rows obtained by substracting the previous row from the current row.

    Like this...
    0
    0
    1
    1
    0
    3

    It would be better, if I the following also,
    1. No calculation done on 1st row (or) 1st & 2nd rows - if both are the same.
    2. Instead of substracting the previous row, the row before that should be substracted - in places where the current row & previous row are the same. Also, No calculation done when the previous row becomes the current row.
    Like this...
    5th row --> 1 (5th row-3rd row, since 5th & 4th rows are the same &
    4th row --> No Calculation to be done (or) atleast Value 0.

    My requirement would be like this:

    (No Value) <-- Considering 1st option
    (No Value) <-- Considering 1st option
    1
    (No Value) <-- Considering 2nd option
    1 <-- Considering 2nd option
    3


    Thanks.

  2. #2
    Forum Contributor
    Join Date
    02-15-2005
    Location
    Blackpool, UK
    Posts
    137
    Quote Originally Posted by Prem
    Also, No calculation done when the previous row becomes the current row.
    Sorry, couldn't work out what you wanted to happen here. Hope the following helps some though.

    In a cell which is NOT row 1 or row 2, put:
    =IF(ROW()=1,0,IF(A3=A2,IF(ROW()=2,0,A3-A1),A3-A4))

    The above assumes you put the formula somewhere on row 3, and the the data in th column A. Please amend accordingly.

    You can then copy this to other cells, including rows 1 and 2. Note that for rows 1 and 2 you may see #REF, but this is OK as the formula should not trigger the offending reference.

    HTH

  3. #3
    Registered User
    Join Date
    09-30-2007
    Posts
    10
    Sir, Thank You for the formula. But in some places, I am not getting it correct (May be fault of Mine).

    I am attaching a sample file.

    1. Here my original column is A & i want the values in Column E (for example)

    2. Is it possible to apply the formula for all the (15) rows Simultaneously
    (Leaving the Empty Row in between)

    3. Plz. Check Whether Ur Formula work here as per my required result (Sorry, I am new to Excel & I could not understand the formula)

    Thanks.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1