How to use LARGE function using criteria. AKA "How can I get the #1 risk based on criteria

Options

I included all this information in the snippet but here is my issue.

The issue is I cannot get a formula where the "Risk/Issue" column identifies whether it is a RISK or an ISSUE or an RISK BECOME ISSUE, and then give me the #1 item depending on that criteria, then the #2 item depending on that criteria, etc.

My current formula: =INDEX(COLLECT(Risks:Risks, [Total Risk Score]:[Total Risk Score], LARGE([Total Risk Score]:[Total Risk Score], 1)), 1)

reads "#1 Risk" based on highest "Risk Score". This is great. Then reads "#2 Risk" on second highest "risk score". This also is great. Using the LARGE function this is possible, but I need a little more!

What I want it to do is read the "#1 Risk" when the "Risk/Issue" column is "Risk". Then the #2 Risk when column states Risk.
I also want the formula to catch the "#1 Issue" when the "Risk/Issue" column is "Issue". Then #2 Issue when column states Issue.
Same for when "Risk/Issue" column is "Risk Became Issue". #2, #3, etc.

some other examples I have tried to no avail:

=INDEX(COLLECT(Risks:Risks, [Risk / Issue]:[Risk / Issue], "Issue"), [Risk #]@row)

=INDEX(COLLECT(Risks:Risks, [Risk / Issue]:[Risk / Issue], ="Issue"), 1, 1)

Tags:

Best Answer

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    edited 05/22/24 Answer ✓
    Options

    Hi @Matthew Campbell

    This formula should work for you, these are for the summary issue fields:

    1st issue

    =INDEX(COLLECT(Risks:Risks, [Risk / Issue]:[Risk / Issue], "Issue", [Total risk score]:[Total risk score], LARGE(COLLECT([Total risk score]:[Total risk score], [Risk / Issue]:[Risk / Issue], "Issue"), 1)), 1)

    2nd issue

    =INDEX(COLLECT(Risks:Risks, [Risk / Issue]:[Risk / Issue], "Issue", [Total risk score]:[Total risk score], LARGE(COLLECT([Total risk score]:[Total risk score], [Risk / Issue]:[Risk / Issue], "Issue"), 2)), 1)

    3rd issue

    =INDEX(COLLECT(Risks:Risks, [Risk / Issue]:[Risk / Issue], "Issue", [Total risk score]:[Total risk score], LARGE(COLLECT([Total risk score]:[Total risk score], [Risk / Issue]:[Risk / Issue], "Issue"), 3)), 1)

    To edit the formula just amend the highlighted fields below

    Both yellow fields to Risk and then the highlighted green 3 to 1, 2 or 3 for 1st, 2nd or 3rd risk

    Same again for Risk became issue

    Hope that helps

    Thanks

    Paul McGuinness

«1

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!