Count instances meeting certain criteria, stop, then start counting again
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
Solved! Go to Solution.
- Labels:
- Expression
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you Jonathan! Glad to know that I was on the right track!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
No problem at all!