Show how many weeks a status has stayed the same?

Options

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.

Tags:

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 11/15/23 Answer ✓
    Options

    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
    Answer ✓
    Options

    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!

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 11/15/23 Answer ✓
    Options

    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
    Answer ✓
    Options

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!