Select only most recent rows using LARGE?

SteveE
SteveE ✭✭✭
edited 04/11/24 in Formulas and Functions

I thought I would post my issue here since the SS community has been so great at helping solve my other SmartSheet dilemmas!

For each project that the PMO oversees, we have a sheet with a single row of project health metadata. On the first of each month a workflow runs against the metadata sheet and copies the row to a “scorecard” sheet so we have a static “image” of the data. So at the end of the year, it is possible to have up to 12 rows in the scorecard sheet. The period/month and workflow run dates are recorded each month (example below).

I want to create a report from the scorecard sheet, but only show the most recent 3 months. In the example below, I'd like to only show the data from months 2, 3 and 4. And in month 6, only show data from periods 4,5 and 6.

Would the LARGE function work if I created a helper row and scanned the period column and added some text in the helper column for the 3 largest periods? And what would the formula look like? I have never used LARGE.

As always, any advice or help would be appreciated.

Best Answer

  • SteveE
    SteveE ✭✭✭
    Answer ✓

    Issue solved. The Period field was copied from another sheet using workflow. For some odd reason, the Period field in the originating sheet had a leading non-numeric character which the LARGE function could not interpret. When I cleaned up the source data, the formula works exactly as planned.

Answers

  • SteveE
    SteveE ✭✭✭

    Update: Here is the formula I am using, but the results are not what I was hoping for.

    =IF(Period@row = LARGE(Period1:Period12, 1), 1, IF(Period@row = LARGE(Period1:Period12, 2), 2, IF(Period@row = LARGE(Period1:Period12, 3), 3, 0)))

    Here are the results:

    And this is what I want to get:


  • SteveE
    SteveE ✭✭✭
    Answer ✓

    Issue solved. The Period field was copied from another sheet using workflow. For some odd reason, the Period field in the originating sheet had a leading non-numeric character which the LARGE function could not interpret. When I cleaned up the source data, the formula works exactly as planned.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!