+ Reply to Thread
Results 1 to 6 of 6

Identifying when a value in a column stops rising

  1. #1
    Registered User
    Join Date
    02-14-2020
    Location
    Hoboken
    MS-Off Ver
    2019
    Posts
    21

    Identifying when a value in a column stops rising

    I have a spreadsheet with two columns of numbers. I'm trying to identify growth in column "B' and specify when the value in column "B" stops rising. (IE: cell "B4<B3"). When this happens, I want to return difference between the cell that ended the rising trend ("B4") and the cell in the adjacent column where the rising trend began ("A2"). I've highlighted specific values that correspond to the requirements i'm asking for.

    I want to return a "0" for when neither of these requirements are met.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Identifying when a value in a column stops rising

    Okay, your columns have 3 colorcodes (orange, green and white). What do they signify?
    So B5 < B4, does that constitute a calculation or not because it is part of the drop began in B4?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    02-14-2020
    Location
    Hoboken
    MS-Off Ver
    2019
    Posts
    21

    Re: Identifying when a value in a column stops rising

    I color coded them so it was easier to identify the trend I'm attempting to capture. All i want to know is, is there an excel function that can dictate when a series of numbers stops rising.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,421

    Re: Identifying when a value in a column stops rising

    This proposal employs two helper columns (C:D) which may be moved and/or hidden for aesthetic purposes.
    Column C identifies where the values in column B stop rising using: =AND(B1<=B2,B2<=B3,B3>B4)
    Column D identifies the value in column A corresponding to where the rising trend begins using: =OR(A2="",AND(B2>B3,B3<=B4))
    Column E yields the difference using: =IF(C4=FALSE,"",B4-INDEX(A$3:A3,AGGREGATE(14,6,(ROW(A$3:A3)-ROW(A$2))/(D$3:D3=TRUE),1)))
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    02-14-2020
    Location
    Hoboken
    MS-Off Ver
    2019
    Posts
    21

    Re: Identifying when a value in a column stops rising

    Firstly, thank you so much for your help. I was looking at your formula and it gave me exactly what I wanted. Could you please explain how this part of the formula works: "B4-INDEX(A$3:A3,AGGREGATE(14,6,(ROW(A$3:A3)-ROW(A$2))/(D$3:D3=TRUE),1"?

    I would seriously appreciate it.
    Last edited by AliGW; 06-09-2020 at 01:33 AM. Reason: Please don't quote unnecessarily!

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,421

    Re: Identifying when a value in a column stops rising

    It is subtracting the value in the cell of column A for which the corresponding value of that row* in column D is true from the value in column B of the row which contains the formula.
    *Note that it only selects from rows previous to the row in which the formula is located.
    The 14 tells the AGGREGATE function to select the largest row for which column D is true as there may be many.
    The 6 tells the AGGREGATE function to ignore errors (#DIV/0 errors in this case) which will occur for each value of false in column D as TRUE/FALSE are Boolean values which Excel treats as 1 and zero respectively.
    I would suggest selecting cell E17 and utilizing the Evaluate Formulas feature to get a better idea of how the formula works.
    let us know if you have any questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Identifying Rising trends in Columns
    By chkn89 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-31-2020, 02:08 PM
  2. Identifying rising values and finding the difference between adjacent cells.
    By peeweelee in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-30-2019, 07:59 PM
  3. how to factor rising income into a future value calculation
    By ianjemmett in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-26-2017, 12:15 PM
  4. Formula for Total Rising Invoice Payments
    By JamMad in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-26-2016, 12:10 PM
  5. Replies: 2
    Last Post: 06-05-2013, 08:59 PM
  6. Help with formula for yearly rising cost spreadsheet please.
    By snowy2006 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-13-2007, 12:50 PM
  7. [SOLVED] Need vertical separators rising from the X axis.
    By Peter Jason in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-02-2006, 08:00 AM

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