Weird outcome with Large function

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:

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:

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.

Find more posts tagged with

Formulas

functionality

Accepted answers

Kelly Moore

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

All comments

Kelly Moore

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

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? :/

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

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.