Count consecutive column data instances in Excel using formula

1
votes

I have an excel sheet which captures the number of orders that the Customer has made every month. I now require a column which populates the number of times when the customer made orders in 2 consecutive months. The excel looks something like this:

enter image description here

Here in the excel orders placed in consecutive months add up to the count. Say if I have orders in 4 consecutive months it sums to 2 in the count column and 3 shows up in the count column when there are orders for 6 consecutive months.

Taking Customer C1 as example, the first set of months with consecutive orders under them are April and May which map as 1. As we have already captured May in our calculation moving on from June the next set of consecutive months with orders that is encountered is August and September which map as 1. Moving ahead with October there are no more months with consecutive orders. So it sums to 2 for C1 which is mapped to Column M.

I have currently populated the column "M" data for reference but as the customer data becomes huge this is pretty tedious. I am trying to use FREQUENCY to do it but am somehow faltering to come up with a proper excel formula for this scenario. Can anyone help me this?

TIA

2
excelexcel-formulaexcel-2016
How do you get a result of 2 for the orders in 2 consecutive months for C1? There are apr&may, may&jun, aug&sep, sep&oct, which is 4... But you show a result of 2 in M2. – Solar Mike
Here as April & May are considered as consecutive months. As May is already calculated the next set which starts from June is combined with July when there are no orders so the consecutive months is 1 here. The same applies for Sept,Oct and November where the consecutive is 1. This sums up to 2 – Suraj Nair
November cannot be included as it is 0... You need to explain this clearly in the original question, not just leave it in a comment. – Solar Mike
@SolarMike Have edited now – Suraj Nair

2 Answers

3
votes

I'm thinking that you need to divide the frequencies (run lengths of consecutive orders) by 2 using integer division so

1->0
2->1
3->1
4->2

etc.

=SUM(QUOTIENT(FREQUENCY(IF($B2:$L2>0,COLUMN($B2:$L2)),IF($B2:$L2=0,COLUMN($B2:$L2))),2))

enter image description here

Again the formula has to be entered using CtrlShiftEnter

2
votes

You are making it quite hard on yourself, and I don't really know how this would be any easier than to calculate how ofter a streak of 2 occurs and add how ofter a streak of 4 occurs etc.etc.

A formula for counting streaks of minimum of two would look like:

=SUM(IF(FREQUENCY(IF($B2:$L2>0,COLUMN($B2:$L2)),IF($B2:$L2=0,COLUMN($B2:$L2)))>1,1))

Entered through CtrlShiftEnter

To take into account the possibilities of a steak of 4, 6, 8, 10 or 12, you can check the above formula against a transposed array (done through seperating values with a semi-colon) like so:

=SUM(IF(FREQUENCY(IF($B2:$L2>0,COLUMN($B2:$L2)),IF($B2:$L2=0,COLUMN($B2:$L2)))>{1;3;5;7;9;11},1))

Entered through CtrlShiftEnter

enter image description here

Maybe some mastermind can shorten this even further :)