Show how many weeks a status has stayed the same?

Asking this question again in case there are any more ideas -

I have a sheet where a weekly status update is logged every week on a new row, including the Health "trend" (Up, Down, Sideways). I want to count the number of weeks that the trend has stayed the same, i.e., how long it has been sideways/up/down. The only formulas I've come up with so far count the number of times it has been sideways in the whole column, but I only want the number of weeks since it most recently changed.

Here is how it would ideally look:

Any help would be greatly appreciated! you can see I've tried some other things with Line ID and Row # but so far can't figure it out.

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

You are going to need a checkbox helper column (called "Trend Start" in this example) with this column formula:

=IF(IFERROR(INDEX([Health Trend]:[Health Trend], MATCH(MAX(COLLECT([Week Of]:[Week Of], [Week Of]:[Week Of], @cell < [Week Of]@row)), [Week Of]:[Week Of], 0)), "") <> [Health Trend]@row, 1)

Then you are going to want a date type column (called "Trend Start Date" in this example) with this column formula:

=MAX(COLLECT([Week Of]:[Week Of], [Week Of]:[Week Of], @cell <= [Week Of]@row, [Trend Start]:[Trend Start], @cell = 1))

And then this would go in the column that shows how many weeks the trend has been running:

=COUNTIFS([Trend Start Date]:[Trend Start Date], @cell = [Trend Start Date]@row, [Week Of]:[Week Of], @cell <= [Week Of]@row)

Caroline Henne

this works, thank you!! I couldn't figure out the right combo - I didn't think about using @cell to look at the Week of the current row.

This will be really useful for overall trend analysis in our status updates!

All comments

Paul Newcome

You are going to need a checkbox helper column (called "Trend Start" in this example) with this column formula:

=IF(IFERROR(INDEX([Health Trend]:[Health Trend], MATCH(MAX(COLLECT([Week Of]:[Week Of], [Week Of]:[Week Of], @cell < [Week Of]@row)), [Week Of]:[Week Of], 0)), "") <> [Health Trend]@row, 1)

Then you are going to want a date type column (called "Trend Start Date" in this example) with this column formula:

=MAX(COLLECT([Week Of]:[Week Of], [Week Of]:[Week Of], @cell <= [Week Of]@row, [Trend Start]:[Trend Start], @cell = 1))

And then this would go in the column that shows how many weeks the trend has been running:

=COUNTIFS([Trend Start Date]:[Trend Start Date], @cell = [Trend Start Date]@row, [Week Of]:[Week Of], @cell <= [Week Of]@row)

Caroline Henne

this works, thank you!! I couldn't figure out the right combo - I didn't think about using @cell to look at the Week of the current row.

This will be really useful for overall trend analysis in our status updates!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.