I have multiple ‘people’ in rows in excel, with 10 columns of system usage data. I want the final column to show how many consecutive months have 0 usage. For example, person a might have had 0 usage in March and April, used in June, then 0 usage in July - December, I would need the end column to show 6.
CodePudding user response：
I've made something for you, based on the numbers of the months in the year: 1-12 (in column "item").
In the "Value" column, there are the values zero or else.
The "Length" column contains the number of subsequent zeroes, based on following formulas:
- First entry :
=IF(B2=0,1,0) - Following entries:
=IF(B3=0,C2 1,0)
Screenshot:
Have fun!
CodePudding user response：
If your data is from B2 to M2 you could use the following formula:
=MAX(FREQUENCY(IF(B2:M2=0,COLUMN(B2:M2)),IF(B2:M2<>0,COLUMN(B2:M2))))
This will show the max number of consecutive 0's in that row 2 from column B to M.
Older versions of Excel require this array formula entered with ctrl shift enter