Count instances meeting certain criteria, stop, then start counting again

[cdelaura]

Hello,

I'm wondering if anyone has any ideas of how to solve the puzzle below...

My data starting format is one row per person with an outcome of eligible/reset indicated per week horizontally. The real data set has hundreds of names and weeks up to 38 (and will continue to grow). Simplified version below (attached is an XLS with three people)

1 = most recent week

9 = first (oldest) week in data set

Name	1	2	3	4	5	6	7	8	9
Alex	Eligible	Eligible	Reset	Eligible	Eligible	Eligible	Eligible	Reset	Eligible

Goal: I'm looking to be able to see the average amount of 'eligible' weeks a person consecutively has before they 'reset'.

So for above example, I would expect Alex to show:

• Reset after 1 week (eligible week 9)
• Reset after 4 weeks (eligible weeks 7-4)
• Awaiting next reset - so ignore weeks 1-2
• Average of 1 and 4 = 2.5 weeks

I'm thinking to use Transpose name by week numbers then using the Multirow formula tool, just a little stumped how to configure the formula

Any ideas? Thanks in advance!

[Jonathan-Sherman · Accepted solution]

Hi @cdelaura,

You could as you said use a transpose to pivot followed by a series of multi-row tools, summarising to create your average at the end!

I've attached my workflow for you to download if needed!

Kind regards,

Jonathan

[cdelaura]

Thank you Jonathan! Glad to know that I was on the right track!

[Jonathan-Sherman]

No problem at all!