Show how many weeks a status has stayed the same?

Caroline Henne

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.

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

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. 👍️