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

Matthew Campbell

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)

Paul McGuinness

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

Paul Newcome

You would use a LARGE/COLLECT combo very similar to the INDEX/COLLECT syntax.

Paul McGuinness

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

Matthew Campbell

@Paul McGuinness - that's it! It worked beautifully. Thank you! The only issue is that it returns an "# INVALID VALUE" error if there are not 3 risks or issues on the log, which poses an issue (ironically). I tried to nest the formula in an IFERROR but that did not work. I played around with IF statements to see if I could somehow state that if there was not a 2nd issue on the log, a " " would result (rather than "#INVALID VALUE". I can make do with this (it is a huge blessing - I appreciate you), but if you have any ideas at how I can make a "" or "N/A" show up if there is not a issue defined, that would be a huge help!

Paul Newcome

Why not try an INDEX/MATCH? Maybe that will allow you to leverage the IFERROR.

=IFERROR(INDEX(RIsks:Risks, MATCH(LARGE(COLLECT([TOtal Risk Score]:[Total Risk Score], [Risk / Issue]:[Risk / Issue], @cell = "Risk")), [TOtal Risk Score]:[Total Risk Score], 0)), "N/A")

Matthew Campbell

@Paul Newcome I keep getting a an error message, not sure exactly where it is hitting (maybe the @cell="risk"?), but your formula works logically, just something is not registering. Any thoughts?

Paul Newcome

That's my fault. I am so used to using MAX in this type of formula, I forgot to include the second portion of the LARGE function to denote 1st, 2nd, 3rd, etc.. Sorry about that.

=IFERROR(INDEX(RIsks:Risks, MATCH(LARGE(COLLECT([TOtal Risk Score]:[Total Risk Score], [Risk / Issue]:[Risk / Issue], @cell = "Risk"), 1), [TOtal Risk Score]:[Total Risk Score], 0)), "N/A")




Matthew Campbell

@Paul Newcome that's it! Why didn't I think of that? Well done! This is helping us greatly

Paul Newcome

Happy to help. 👍️

Matthew Campbell

@Paul Newcome

Paul! When I built out a risk log for a project, the formula above broke down. I tried to figure out where, but I can't seem to nail it down. The @cell = "Risk" is not picking up "Risk".. this formula still searches the largest number but disregards the @Cell="" for the criteria. Can you see where this is happening in the formula? I cannot…

formula: =IFERROR(INDEX(RISKS:RISKS, MATCH(LARGE(COLLECT([Total Risk Score]:[Total Risk Score], [Risk / Issue]:[Risk / Issue], @cell = "Risk"), 1), [Total Risk Score]:[Total Risk Score], 0)), "N/A")

Paul Newcome

@Matthew Campbell Let's try a troubleshooting formula. What do you get from this?

=COUNTIFS([Risk / Issue]:[Risk / Issue], @cell = "Risk")

Matthew Campbell

@Paul Newcome

Thanks for the reply! I am still messing around with it, trying to figure it out. So that counted risks, issues, and risk became issues, fine. I even changed the number of all types to make sure it was counting correctly. I tested other risk logs and the same issue is happening. Somehow when the log is filled out with multiple Issues, Risks, and Risks Became Issues, the formula starts to break down and pick up incorrectly.

Paul Newcome

What does this give you?

=LARGE(COLLECT([Total Risk Score]:[Total Risk Score], [Risk / Issue]:[Risk / Issue], @cell = "Risk"), 1)

Matthew Campbell

@Paul Newcome

That picks up the correct risk scores, which is awesome! Now how do we let it give us the risk name associated with those scores? Maybe an entire separate formula drawing from this formula? Except duplicates may interfere with that idea..

Paul Newcome

What do we get with this one?

=MATCH(LARGE(COLLECT([Total Risk Score]:[Total Risk Score], [Risk / Issue]:[Risk / Issue], @cell = "Risk"), 1), [Total Risk Score]:[Total Risk Score], 0)

Matthew Campbell

@Paul Newcome

That is so trippy! It brings back the Row #