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

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

2nd issue

3rd issue

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 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!

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")

@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?

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")

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

Happy to help. 👍️

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")

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

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

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.

What does this give you?

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

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..

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)