SailorJerry7030
New Member
- Joined
- Apr 27, 2018
- Messages
- 30
Good Morning All!
I hope I have an easy one for you this morning. I've got about 96k rows of policy numbers and 12 columns starting in column B (Jan-Dec). If a policyholder called within that month it displays how many times they called. I'm not concerned with how many calls they made, only if there is a value > 0 within that cell. The goal here is to sum the clusters of consecutive months they called in. If they call in just in January but not in February, I don't care. If they call in January and February, but not March, that would be considered 1 cluster. If they called in January, February, March, not in April, not in May, called in June, not called in July, called in August, called in September, didn't call us the rest of the year, the sum of clusters would be 2 (Jan-Mar) and (Aug-Sept), don't care about the 1 off month of June where they called since it's not a cluster. I've been trying to incorporate code similar to "=SUM(--((FREQUENCY(IF(A2:H2>=50, COLUMN(A2:H2)),IF(A2:H2<50,COLUMN(A2:H2))))>=3))" granted this series of code is for a completely different project unrelated, but I feel like it could be applicable by changing the cells and values (50 and 3).
I hope this goal makes sense and someone can help. If you need anymore information, let me know. The simpler the better.
I hope I have an easy one for you this morning. I've got about 96k rows of policy numbers and 12 columns starting in column B (Jan-Dec). If a policyholder called within that month it displays how many times they called. I'm not concerned with how many calls they made, only if there is a value > 0 within that cell. The goal here is to sum the clusters of consecutive months they called in. If they call in just in January but not in February, I don't care. If they call in January and February, but not March, that would be considered 1 cluster. If they called in January, February, March, not in April, not in May, called in June, not called in July, called in August, called in September, didn't call us the rest of the year, the sum of clusters would be 2 (Jan-Mar) and (Aug-Sept), don't care about the 1 off month of June where they called since it's not a cluster. I've been trying to incorporate code similar to "=SUM(--((FREQUENCY(IF(A2:H2>=50, COLUMN(A2:H2)),IF(A2:H2<50,COLUMN(A2:H2))))>=3))" granted this series of code is for a completely different project unrelated, but I feel like it could be applicable by changing the cells and values (50 and 3).
I hope this goal makes sense and someone can help. If you need anymore information, let me know. The simpler the better.
