# Finding last date with positive number in dataset

1. ## 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. ## 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. ## Re: Finding last date with positive number in dataset

With your data table in sheet1
Formula:
`Please Login or Register  to view this content.`

4. ## Re: Finding last date with positive number in dataset

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

5. ## 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. ## 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:
`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. ## 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. ## 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. ## 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. ## Re: Finding last date with positive number in dataset

For the start date try this modification of ChemistB's formula:
Formula:
`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.

11. ## 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:

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