Weird outcome with Large function

Shimanta Roy
Shimanta Roy ✭✭✭✭
in Formulas and Functions

I am experiencing a weird scenario while trying to use the Large function. What I want to do is to get the two most recent dates from a column with numerous dates. Please check the sample datasheet:

sc1.PNG

The Dates column contains the dates and I am showing the two most recent dates in the column Recent Dates. I am using the following two formulas for that, and it is working fine with this dataset as you see.

=LARGE(Dates:Dates, 1) which gave me the most recent date, 06/07/21

=LARGE(Dates:Dates, 2) which gave me the second most recent date, 05/24/21

Up to this point the function is working fine and showing accurate outcome. However, please see the below dataset and outcome:


sc2.PNG

The formulas are the same, and the outcome should be 05/24/21 as most recent date, and 05/17/21 as the second most recent date. But it is showing 05/24/21 for the both. I have tried adding new rows with new past dates, but always it remains as 05/24/21 for both the values. So, the function is not working. I have saved the sheet after any changes, without any difference.

I was just wondering what could be the issue here. Anyone with any idea regarding this? Thank you.

Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hello @Shimanta Roy

    The results are not incorrect- with multiple instances of 5/24 then all of those instances would be 1st, and 2nd, through x. I believe what you asking is what is the Distinct Large instances.

    Try this

    =LARGE(DISTINCT(COLLECT(Dates:Dates, Dates:Dates, <>"")), 1)

    This should find your Distinct values, then select the large values from that collection.

    cheers

    · Share on FacebookShare on Twitter

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hello @Shimanta Roy

    The results are not incorrect- with multiple instances of 5/24 then all of those instances would be 1st, and 2nd, through x. I believe what you asking is what is the Distinct Large instances.

    Try this

    =LARGE(DISTINCT(COLLECT(Dates:Dates, Dates:Dates, <>"")), 1)

    This should find your Distinct values, then select the large values from that collection.

    cheers

    · Share on FacebookShare on Twitter
  • Shimanta Roy
    Shimanta Roy ✭✭✭✭
    https://community.smartsheet.com/discussion/comment/293979#Comment_293979

    Perfect! The formula with Distinct is working as expected. However, I am still confused with my first formula, why in case of multiple instances of 5/24 then all of those instances would be 1st, and 2nd? :/

    · Share on FacebookShare on Twitter
  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Maybe I'm looking at it incorrectly, but 5/24 (with two instances) are the top two max dates in your range. In my mind, LARGE grabs the first instance as Large (range, 1). It then looks at the remain population for your next LARGE and grabs (range2, 2)- which has the 2nd instance in that population of dates. 5/24 is the largest date in that collection. If you had three instances 5/24 would be the top three max dates.

    Kelly

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!