Need Help?? (Not sure what function to use)

Jarodjp42

New Member
Joined
Nov 21, 2017
Messages
35
Hi, I am trying to write a formula that will tell me if a client is new or existing. Below is some sample data of the file I am working with.

ClientNew/Existing Feb-17Mar-17Apr-17May-17Jun-17Jul-17Aug-17Sep-17Oct-17Nov-17Dec-17Jan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18Jan-19Feb-19
JeffExisting $ 227,828 $ 300,773 $ 401,894 $ 301,140 $ 541,519 $ 306,717 $ 233,761 $ 334,775 $ 267,705 $ 247,118 $ 327,502 $ 301,082 $ 268,381 $ 249,657 $ 298,969 $ 233,347 $ 331,162 $ 337,342 $ 226,385 $ 256,811 $ 277,774 $ 234,745 $ 270,612 $ 257,375 $ 220,512
BrianExisting $ 1,317 $ 4,339 $ 2,003 $ 6,548 $ 4,669 $ 159 $ - $ 2,519 $ 7,246 $ 2,568 $ 12,412 $ 4,029 $ - $ 2,324 $ 3,915 $ 3,101 $ 4,718 $ 5,561 $ 3,011 $ 4,979 $ 4,557 $ 7,150 $ 14,316 $ 7,165 $ 2,764
JordanExisting $ 179 $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ 78 $ - $ 100 $ - $ - $ - $ - $ - $ 1,627 $ 1,019 $ 115 $ 101 $ 1,951 $ -
MichaelNew $ 500 $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ 650 $ - $ - $ - $ 26 $ 1,466 $ - $ - $ - $ - $ - $ -
MikeExisting $ - $ - $ - $ - $ 851 $ - $ - $ - $ - $ - $ - $ - $ 396 $ - $ - $ - $ 1,314 $ - $ 99 $ - $ - $ 1,977 $ 629 $ - $ -
JackNew $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ 477 $ 134 $ - $ - $ - $ 976 $ -
AllisonNew $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ 595 $ - $ - $ 1,152 $ - $ - $ - $ -
DanielleNew $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ 1,922 $ - $ - $ - $ - $ - $ -
AshleyExisting $ (1,704) $ 1,832 $ 12,472 $ - $ 9,638 $ 735 $ - $ - $ - $ - $ - $ 1,013 $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ -
ChrisNew $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ -
SarahNew $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ 3,397 $ - $ 63 $ -

<colgroup><col><col><col span="25"></colgroup><tbody>
</tbody>

I am trying to write a formula in column B that will tell me if a client is new or existing. My rules for being a new client is they do not have any revenue for 12 consecutive months. To explain further, we received revenue from Michael in Feb 2017 but then didn't receive any revenue for the next 12 months after that. Because of that Michael's status has now gone from existing to new.

So I am looking for a formula that will look at when a client received revenue and then count the next 12 columns over. If the total adds up to 0 then the clients status should say new. If a client receives revenue 13 months after the last time I received revenue then I want it to start counting from that value. If you can help me write a formula that will do this I would be much appreciated. Thank you tons in advance!!!
 
Last edited:

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Try this formula

<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:66px;" /><col style="width:99px;" /><col style="width:64px;" /><col style="width:61px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:58px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:58px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:61px;" /><col style="width:64px;" /><col style="width:61px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >O</td><td >P</td><td >Q</td><td >R</td><td >S</td><td >T</td><td >U</td><td >V</td><td >W</td><td >X</td><td >Y</td><td >Z</td><td >AA</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Client</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">New/Existing</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">feb-17</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">feb-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">mar-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Apr-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">may-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">jun-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">jul-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Aug-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">sep-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">oct-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">nov-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Dec-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Jan-19</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">feb-19</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Jeff</td><td >Existing</td><td style="text-align:right; ">227,828</td><td style="text-align:right; ">268,381</td><td style="text-align:right; ">249,657</td><td style="text-align:right; ">298,969</td><td style="text-align:right; ">233,347</td><td style="text-align:right; ">331,162</td><td style="text-align:right; ">337,342</td><td style="text-align:right; ">226,385</td><td style="text-align:right; ">256,811</td><td style="text-align:right; ">277,774</td><td style="text-align:right; ">234,745</td><td style="text-align:right; ">270,612</td><td style="text-align:right; ">257,375</td><td style="text-align:right; ">220,512</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Brian</td><td >Existing</td><td style="text-align:right; ">1,317</td><td > </td><td style="text-align:right; ">2,324</td><td style="text-align:right; ">3,915</td><td style="text-align:right; ">3,101</td><td style="text-align:right; ">4,718</td><td style="text-align:right; ">5,561</td><td style="text-align:right; ">3,011</td><td style="text-align:right; ">4,979</td><td style="text-align:right; ">4,557</td><td style="text-align:right; ">7,150</td><td style="text-align:right; ">14,316</td><td style="text-align:right; ">7,165</td><td style="text-align:right; ">2,764</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Jordan</td><td >Existing</td><td style="text-align:right; ">179</td><td > </td><td style="text-align:right; ">100</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">1,627</td><td style="text-align:right; ">1,019</td><td style="text-align:right; ">115</td><td style="text-align:right; ">101</td><td style="text-align:right; ">1,951</td><td style="text-align:right; ">5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Michael</td><td >New</td><td style="text-align:right; ">500</td><td > </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Mike</td><td >New</td><td > </td><td style="text-align:right; ">396</td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Jack</td><td >Existing</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">477</td><td style="text-align:right; ">134</td><td > </td><td > </td><td > </td><td style="text-align:right; ">976</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Allison</td><td >Existing</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">595</td><td > </td><td > </td><td style="text-align:right; ">1,152</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >Danielle</td><td >Existing</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">1,922</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Ashley</td><td >New</td><td style="text-align:right; ">-1,704</td><td > </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; text-align:right; ">-25</td><td style="background-color:#ccff33; text-align:right; ">25</td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >Chris</td><td >New</td><td > </td><td > </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td><td style="background-color:#ccff33; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >Sarah</td><td >Existing</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">3,397</td><td > </td><td style="text-align:right; ">63</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=IF(SUM(OFFSET(C2,0,MATCH(MAX($1:$1),$1:$1,0)-14,,12))=0,"New","Existing")</td></tr></table></td></tr></table> <br /><br />
 

Jarodjp42

New Member
Joined
Nov 21, 2017
Messages
35
@DanteAmor Thank you for your response. However, the formula isn't doing exactly what I am looking for. Sorry for the poor explanation. Hopefully, I can explain it a little better this time.

I have a total of 25 trailing months from the end of the month today. I am looking for a formula that will look at the first month (Feb-17) and if there is revenue move to the next column and so on until it finds a month with no revenue. Once it finds a month with no revenue I want it to sum that column and the next 11, so a total of 12 months. If the total of the 12 months equals 0 then I want the status of the client to be "New". I am also looking for a formula (hopefully we can incorporate this into the same formula) that will look for the first amount of revenue and if it was in 2019 then return the status of "New". Another problem I am trying to figure out is that with this formula summing up the next 12 months after a month with revenue, how do I get the status to stay existing if it sums up the next couple of months after a month with revenue but there are no more months with data. I still want the status to stay existing even tho when you sum up those months it is going to equal 0. Hopefully, you can help me with my current situation. I appreciate the help you can provide. Thank you!

PS if you need more explanation please feel free to reach back out to me! Thanks again!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
It would be very helpful if you explain with examples each of the cases that can be presented. In this way we can prepare a formula that covers all cases.
 

Jarodjp42

New Member
Joined
Nov 21, 2017
Messages
35

ADVERTISEMENT

@DanteAmor Hopefully this helps. Thanks again for your time and help!

My criteria:

- If the first month of revenue received comes in 2019 status should be "New"
- If we don't receive any revenue for 12 consecutive months status should be "New"
- If we receive revenue after 12 months of no revenue for a client the following year should have the status of "1st full year"
- Otherwise, the client's status should be "Existing" if we have revenue for the client.


ClientNew/Existing17-Feb17-Mar17-Apr17-May17-Jun17-Jul17-Aug17-Sep17-Oct17-Nov17-Dec18-Jan18-Feb18-Mar18-Apr18-May18-Jun18-Jul18-Aug18-Sep18-Oct18-Nov18-Dec19-Jan19-Feb
Michael New00000000000000000000000100300

<colgroup><col><col><col span="25"></colgroup><tbody>
</tbody>

Example, we received the first month of revenue in 2019 so status is "New"

ClientNew/Existing17-Feb17-Mar17-Apr17-May17-Jun17-Jul17-Aug17-Sep17-Oct17-Nov17-Dec18-Jan18-Feb18-Mar18-Apr18-May18-Jun18-Jul18-Aug18-Sep18-Oct18-Nov18-Dec19-Jan19-Feb
JohnNew2000003002001500000000000000020012000


<colgroup><col><col><col span="25"></colgroup><tbody>
</tbody>

Example, we received revenue in June & July of 2017 but then didn't receive revenue for the next 12 months so that should change the clients status to "New".

ClientNew/Existing17-Feb17-Mar17-Apr17-May17-Jun17-Jul17-Aug17-Sep17-Oct17-Nov17-Dec18-Jan18-Feb18-Mar18-Apr18-May18-Jun18-Jul18-Aug18-Sep18-Oct18-Nov18-Dec19-Jan19-Feb
JamesNew20035000000000000003000000020012000

<colgroup><col><col><col span="25"></colgroup><tbody>
</tbody>

Example, we received revenue from James in Feb & Mar 2017 but then didn't receive any revenue for 12 consecutive months. Once we received revenue again the clients status should be "New" until Jan 2019 and at that point, it should change from "New" to "1st Full Year"

ClientNew/Existing17-Feb17-Mar17-Apr17-May17-Jun17-Jul17-Aug17-Sep17-Oct17-Nov17-Dec18-Jan18-Feb18-Mar18-Apr18-May18-Jun18-Jul18-Aug18-Sep18-Oct18-Nov18-Dec19-Jan19-Feb
JakeExisting$1,317$4,339$2,003$6,548$4,669$159$ -$2,519$7,246$2,568$12,412$4,029$ -$2,324$3,915$3,101$4,718$5,561$0$0$0$0$0$0$0

<colgroup><col><col><col span="25"></colgroup><tbody>
</tbody>

Example, we have received revenue from jake on a regular basis which means he should have a status of "Existing". However, I still want to have the "Existing" status for Jake even though we haven't received any revenue for the last 7 months.

I am hoping to get a formula that can look column by column until it finds revenue and then calculates out 12 months going forward to see if there is revenue or not. If no revenue for 12 consecutive months status is "New"

I hope this makes it a little more clear. If not please let me know and I can provide more detail. Thanks!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
For that you need 2 auxiliary columns and the following 3 array formulas.

<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:66px;" /><col style="width:99px;" /><col style="width:99px;" /><col style="width:99px;" /><col style="width:64px;" /><col style="width:66px;" /><col style="width:69px;" /><col style="width:63px;" /><col style="width:68px;" /><col style="width:64px;" /><col style="width:69px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >Y</td><td >Z</td><td >AA</td><td >AB</td><td >AC</td><td >AD</td><td >AE</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Client</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">New/Existing</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Aux 1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Aux 2</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">feb-17</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">oct-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">nov-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">dic-18</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">ene-19</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">feb-19</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">mar-19</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">abr-19</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Jeff</td><td >New</td><td style="text-align:right; ">29</td><td style="text-align:right; ">24</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">80</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Brian</td><td >New</td><td style="text-align:right; ">9</td><td style="text-align:right; ">12</td><td > </td><td style="text-align:right; ">5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Michael</td><td >Existing</td><td style="text-align:right; ">5</td><td style="text-align:right; ">2</td><td style="text-align:right; ">1</td><td style="text-align:right; ">6</td><td > </td><td style="text-align:right; ">7</td><td > </td><td style="text-align:right; ">8</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Jordan</td><td >1st Full Year</td><td style="text-align:right; ">9</td><td style="text-align:right; ">16</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >{=IF(MATCH(YEAR(TODAY()),YEAR($1:$1),0)<=C2,"New",IF(D2>=12,IF(SUM(OFFSET(A2,0,MATCH(2019,YEAR($1:$1),0)-1,,MONTH(TODAY())))>0,"1st Full Year","New"),"Existing"))}</td></tr><tr><td >C2</td><td >{=MIN(IF(OFFSET(E2,0,0,,MATCH(MAX($1:$1),$1:$1,0))<>0,COLUMN(OFFSET(E2,0,0,,MATCH(MAX($1:$1),$1:$1,0)))))}</td></tr><tr><td >D2</td><td >{=MAX(FREQUENCY(IF(E2:AE2=0,COLUMN(E2:AE2)),IF(E2:AE2<>0,COLUMN(E2:AE2))))}</td></tr></table></td></tr></table> <br /><br />


Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
 
Last edited:

Jarodjp42

New Member
Joined
Nov 21, 2017
Messages
35

ADVERTISEMENT

@DanteAmor Thank you so much. This is exactly what I was looking for. Can you explain to me what columns C & D are doing? So I have a better understanding of the formula. Once again thank you for all your help!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
@DanteAmor Thank you so much. This is exactly what I was looking for. Can you explain to me what columns C & D are doing? So I have a better understanding of the formula. Once again thank you for all your help!

I'm glad to help you. Thanks for the feedback.


Column C is to know when they made the first payment.
Column D is to count how many consecutive cells have 0
 
Last edited:

Jarodjp42

New Member
Joined
Nov 21, 2017
Messages
35
@DanteAmor after incorporating the formulas you provided me to get my desired outcome. I came across one instance that I am not sure how to fix. I was hoping you could help if possible. Here is my issue with the current formula you provided me.

{=MAX(FREQUENCY(IF(E2:AE2=0,COLUMN(E2:AE2)),IF(E2:AE2<>0,COLUMN(E2:AE2))))}

The specific issue I am running into is when a client, max, for example (shown below), doesn't have any revenue for the first 12 months (Mar 2017 - Feb 2018). Then we receive revenue on the 13th month, (Mar 2018) and then go another 11 months without revenue (Apr 2018 - Feb 2019) and then received revenue on the 12th month after Mar 2018, (Mar 2019). The status for Max should be Existing but the formula has it as "New" because it is pulling the 12 consecutive months without revenue. What I actually want the formula to do is pull the 11 consecutive months without revenue. The formula is using the max function to find the max amount of consecutive months without revenue after it calculates the first part of the formula. So for this specific example, the formula would read {=Max(12,11,0)}. So instead of it pulling the max number of consecutive months without revenue (12), I am looking for something that pulls the later value of consecutive months (11) and changing the status of the client to "Existing". Do you know of a function or formula that would do the same thing that you already helped me with but instead pull the later consecutive months without revenue instead of the max amount of consecutive months without revenue? Thank you in advance for any help or direction to can provide. Also, if I have explained it well enough or you need more information please let me know! Thanks again.

Mar-17Apr-17May-17Jun-17Jul-17Aug-17Sep-17Oct-17Nov-17Dec-17Jan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18Jan-19Feb-19Mar-19
Max00000000000060000000000000400

<colgroup><col width="64" span="26" style="width:48pt"> </colgroup><tbody>
</tbody>
 

Jarodjp42

New Member
Joined
Nov 21, 2017
Messages
35
@DanteAmore Here is another example I wanted to provide you to maybe better clarify for you so you know exactly what I am trying to accomplish.

Below I have a client, Adam. In column D (Aux 2) I get 12 when I use the following formula {=MAX(FREQUENCY(IF(E2:AE2=0,COLUMN(E2:AE2)),IF(E2:AE2<>0,COLUMN(E2:AE2))))} however what I am trying to get is 7 instead of 12. The 12 is getting returned because of the max function in the formula. I am not sure how to return the 7. Basically, I am looking for the last set of consecutive months without revenue after the last month that revenue was received. So is there another function I can replace MAX with to look at the data and find the last column with revenue and then count the consecutive months with no revenue? Thanks!


ClientstatusAux 1Aux 2Mar-17Apr-17May-17Jun-17Jul-17Aug-17Sep-17Oct-17Nov-17Dec-17Jan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18Jan-19Feb-19Mar-19
AdamExisting1312 $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ 860 $ 12,678 $ - $ 955 $ 29,656 $ 6,105 $ - $ - $ - $ - $ - $ - $ -
ClientstatusAux 1Aux 2Mar-17Apr-17May-17Jun-17Jul-17Aug-17Sep-17Oct-17Nov-17Dec-17Jan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18Jan-19Feb-19Mar-19
AdamExisting137 $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ 860 $ 12,678 $ - $ 955 $ 29,656 $ 6,105 $ - $ - $ - $ - $ - $ - $ -

<colgroup><col width="64" span="29" style="width:48pt"> </colgroup><tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,109,453
Messages
5,528,876
Members
409,843
Latest member
akostaki
Top