Using LARGE with duplicate results?

Jaye Casciano

Looking to find, for example, the 10 largest projects in our system by allocated resource hours. But what happens when there are duplicates/projects with the same number of allocated resource hours?

=LARGE(({total hours}),n)

From the result of that formula, in another cell, I then collect that respective project's name using the ref {project name}. So far I've been using this formula as a workaround:

=INDEX((COLLECT({project name}, {total hours}, [cell with LARGE formula]@row)), 1, 1)

However, when the LARGE formula returns two of the same numbers, the INDEX/COLLECT pulls only the first result.

How can I modify the formula to return the "second" project's name? To that end, is there a way I can put this altogether into one formula so I don't run this risk in the first place?

Ramzi K

@Jaye Tatone

I get it now (I think :)).

OK, it's a bit complicated and includes some creative Smartsheet formula "magic", but here it is (again with Smartsheet there is more than one way to do what you need, but here is one way):

Your reference sheet where your master project list is will look like this:

TheHours+column will contain this formula (remember your column names are different so you will need to change to accommodate):

=IF(AND([email protected]>= LARGE(Hours:Hours, 10),[email protected]<= LARGE(Hours:Hours, 1)), IF(COUNTIF(Hours$1:[email protected],[email protected]) > 1,[email protected]+ (COUNTIF(Hours$1:[email protected],[email protected])),[email protected]))

What this does is say "If the project is in the top 10and这是第一次出现的时间,然后把the hours, otherwise add 1 to the hours. (this is for ranking only and will not affect the hours you pull into your sheet).

TheRankcolumn uses the RANKEQ function to rank all your projects based on Hours+:

=IF([Hours+]@row <> "", RANKEQ([Hours+]@row, [Hours+]:[Hours+]), "")

Next, your main sheet will now pull in values based on the Rank. So formulas below are:

Projectcolumn: =INDEX({Project}, MATCH([email protected], {Rank}, 0))

Hourscolumn: =INDEX({Hours}, MATCH([email protected], {Rank}, 0))

I hope this does the trick. If not, let's keep at it. :)

Cheers,

Ramzi

Ramzi K

@Jaye Tatone

Here's one way to do it:

Add a helper column that you will use for your large formula. The helper column will add 1 to each instance that the Hours column repeats. Here's an example of how this can be done:

Helper Column Formula:

[email protected]+ COUNTIF(Hours$1,[email protected])

LARGE cell formula (in bold blue above):

=LARGE(Helper1:Helper3, 1)

Second project with 12 hours cell formula (in bold red above):

=INDEX((COLLECT(Project:Project, Helper:Helper, Hours5)), 1, 1)

You can hide the Helper column in your reference sheet as needed.

I hope this helps.

Cheers,

Ramzi

Jaye Casciano

Hi@Ramzi K, do let me know if I'm just misunderstanding but I'm unclear how this will help me. Might need some hand-holding!

Here's an example of what I'm using right now:

I need this list to always return the top 10 allocated projects (by formula as this list changes regularly). I need both the name of the project as well as the number of hours allocated to show in this list. I'm almost wondering if there's a way I can use LARGE to find the largest # of hours but return the project name. Then, use the project name to INDEX/COLLECT the associated hours since the project names will never be repeated (unlike the hours).

Thoughts? Thanks again for your insight.

David Tutwiler

Have you tried using the DISTINCT function within your LARGER function to only return the distinct elements? Something like:

=LARGE(DISTINCT({total hours}),n)

Ramzi K

@Jaye Tatone

I get it now (I think :)).

OK, it's a bit complicated and includes some creative Smartsheet formula "magic", but here it is (again with Smartsheet there is more than one way to do what you need, but here is one way):

Your reference sheet where your master project list is will look like this:

TheHours+column will contain this formula (remember your column names are different so you will need to change to accommodate):

=IF(AND([email protected]>= LARGE(Hours:Hours, 10),[email protected]<= LARGE(Hours:Hours, 1)), IF(COUNTIF(Hours$1:[email protected],[email protected]) > 1,[email protected]+ (COUNTIF(Hours$1:[email protected],[email protected])),[email protected]))

What this does is say "If the project is in the top 10and这是第一次出现的时间,然后把the hours, otherwise add 1 to the hours. (this is for ranking only and will not affect the hours you pull into your sheet).

TheRankcolumn uses the RANKEQ function to rank all your projects based on Hours+:

=IF([Hours+]@row <> "", RANKEQ([Hours+]@row, [Hours+]:[Hours+]), "")

Next, your main sheet will now pull in values based on the Rank. So formulas below are:

Projectcolumn: =INDEX({Project}, MATCH([email protected], {Rank}, 0))

Hourscolumn: =INDEX({Hours}, MATCH([email protected], {Rank}, 0))

I hope this does the trick. If not, let's keep at it. :)

Cheers,

Ramzi

Jaye Casciano

Hi@Ramzi K, I ended up hacking up your formula a bit and it really helped, thank you so much! I had to apply this as a column formula due to the quick-moving and many-hands nature of the referenced master project sheet. As you know, we can't use absolute references in column formulas, so I ended up using the row's project number (which is a number that will never be repeated), converting it to a number using VALUE, then dividing by 1000 to just get a small number to add to the rank. We now have a true ranking system based on the projects' sizes in the reference sheet. In my data sheet I can easily use this ranking system to collect the project name, category, etc based on it's rank (using SMALL to get the 1st through 10th smallest ranks).

Now, I'm taking this one step further and want to break down these projects even more by their categories. However, I'm getting #INVALID VALUE errors when doing so. Here's what I'm currently using:

=INDEX(COLLECT({Project name}, {Project category}, <>"Category to exclude", {Rank}, SMALL({Rank},n)), 1)

Is there a way I need to rephrase this formula? Essentially I'm getting #INVALID VALUE when it finds a value that IS the category it's supposed to exclude. Hope that makes sense. Here's an example screenshot:

Any ideas?

Ramzi K

@Jaye Tatone

Can you show a screen shot of the reference sheet so I can get the full picture?

Cheers,

Ramzi

Jaye Casciano

@Ramzi Kthere must've been an error with my sheet as when I logged in this morning they worked just fine. I've had that sometimes if I've added and deleted multiple large cross-sheet column references :) all good now. Thanks again very much for your help!

Ramzi K

Glad to help!

Cheers,

Ramzi