+ Reply to Thread
Results 1 to 11 of 11

Finding last date with positive number in dataset

  1. #1
    Registered User
    Join Date
    06-14-2021
    Location
    USA
    MS-Off Ver
    Microsoft 360
    Posts
    12

    Question Finding last date with positive number in dataset

    I have a data set that have months (Jan-18 through Apr-21) in the columns across the top in Row 1 and Customer Numbers (1 through 100,000) in the rows in Col A.

    In each cell within the database (B2-AO100001), each cell has the revenue $ that each customer contributed for each month (there are lots of monthly fluctuations - a customer may go to zero one month and then come back a later month).

    I am trying to write a formula that returns the end date for each customer, which I'm defining as the last month a customer had positive revenue. So for example, if Cust 1 had >0 revenue in Apr-21 (the last month in the dataset), the formula would return Apr-21; if a Cust 2 had >0 revenue in Jan-19 and then every month from Feb-21 through Apr-21 =0, the formula returns Jan-19; if Cust 3 had >0 rev in Jan-19 and then went to zero for a few months and then came back as >0 rev in May-19 before returning to zero for all months Jun-19 through Apr-21, it would return May-19.

    Hope this makes sense. I can't anonymously share share excel files due to my companies privacy controls so apologies for not being able to attached an example. Let me know if there are questions.

    Thanks!

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,862

    Re: Finding last date with positive number in dataset

    I am sure you can generate an anonymised file: customers (CUSTOMER A to CUSTOMER Z)

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

    Re: Finding last date with positive number in dataset

    With your data table in sheet1
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    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

  4. #4
    Registered User
    Join Date
    06-14-2021
    Location
    USA
    MS-Off Ver
    Microsoft 360
    Posts
    12

    Re: Finding last date with positive number in dataset

    I cant remove my full name as the author is what I meant

  5. #5
    Registered User
    Join Date
    06-14-2021
    Location
    USA
    MS-Off Ver
    Microsoft 360
    Posts
    12

    Re: Finding last date with positive number in dataset

    Thanks! My work is all in one tab so no need for the index/match portion of the formula that is tying the tabs together. Struggling to pull this portion of the formula out but I dont fully understand how the aggregate formula works. Can you re-write the formula so it works in column P of sheet 1?

    Really appreciate it

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

    Re: Finding last date with positive number in dataset

    You'd just remove references to Sheet1 and the final match is P1 instead of $a2 (and modifying to cover range of A1:AO100001) so
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The Aggregate formula in this case is looking in the appropriate row (based on customer) and pulling the largest (last) column that is > 0.

  7. #7
    Registered User
    Join Date
    06-14-2021
    Location
    USA
    MS-Off Ver
    Microsoft 360
    Posts
    12

    Re: Finding last date with positive number in dataset

    Oh didnt realize I still needed the index match, think I got it working, thanks so much!

  8. #8
    Registered User
    Join Date
    06-14-2021
    Location
    USA
    MS-Off Ver
    Microsoft 360
    Posts
    12

    Re: Finding last date with positive number in dataset

    Follow up question if you dont mind...also please keep in mind I dont have any negative numbers in this dataset so lets replace your negative numbers with zero

    Next thing I want to do is create a flag (0/1 indicator where 0 = no, doesnt qualify and 1 = yes, does qualify) for each customer row that qualifies for the following rule:

    Trying to identify all customers that leave and return within the timeframe of the data...so if someone has positive revenue then goes to zero and then goes back to having positive revenue within the dataset then I want to return "1" and if they dont "it should return 0"...can you add this into column Q of sheet 1?

    Thanks!

  9. #9
    Registered User
    Join Date
    06-14-2021
    Location
    USA
    MS-Off Ver
    Microsoft 360
    Posts
    12

    Re: Finding last date with positive number in dataset

    Sorry but could you also help me with the formula for start date, thought I had it working but I dont...same concept as end date except that its the first month a customer generated positive revenue...thanks!

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,074

    Re: Finding last date with positive number in dataset

    For the start date try this modification of ChemistB's formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For the flag in column Q try: =(O2>0)+0
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  11. #11
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    365 & WPS(2022)
    Posts
    2,045

    Re: Finding last date with positive number in dataset

    Temporarily borrow ChemistB at post#03 and attach files for use

    worksheet name : sheet2
    Cell B2 array formula , Drag down

    HTML Code: 
    =INDEX(Sheet1!$1:$1,MAX((Sheet1!$A$2:$A$15=$A2)*(Sheet1!$B$2:$O$15>0)*COLUMN(Sheet1!$B$2:$O$15)))

+ 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. Finding the average of top and bottom 10% of a dataset
    By bubblez923 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-07-2020, 05:28 PM
  2. [SOLVED] How to sum a positive and negative number so the total is positive
    By jminty95 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-15-2017, 09:27 PM
  3. Finding the last value recorded in a dataset
    By rburkholder in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-26-2017, 09:46 AM
  4. Finding the smallest positive number from a list?
    By jplank in forum Excel General
    Replies: 2
    Last Post: 03-01-2016, 11:26 AM
  5. [SOLVED] Finding the average of positive numbers base on the previous date
    By top00001 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-27-2015, 03:54 AM
  6. [SOLVED] Finding ALL CAPS in large dataset
    By renee705 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-30-2012, 02:35 PM
  7. Finding first and bottomlast years in large dataset
    By laurafv in forum Excel General
    Replies: 2
    Last Post: 02-28-2011, 09:55 AM

Tags for this Thread

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