LARGE function with IF

Leigh Hayman

I'm trying to create a formula to show the "Nth" highest value for a set of data but only if it meets a certain condition.

The values (to be used for the LARGE function) are either WON or PENDING. I would like to create a formula that indicates the "Nth" highest value but only look at the valies that are "PENDING".

Thanks

Bassam Khalil

Hi @Leigh Hayman 

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Leigh Hayman

Thank you - the sheet is quite large and has a lot of columns so I have attached a screen shot of the relevent data.

I am trying to extract the LARGE value for Pending entries

I hope this helps

Capture.PNG

Paul Newcome

Try incorporating a COLLECT function.

=LARGE(COLLECT({range to collect}, {status range}, "@cell = "Pending"), n)

Leigh Hayman

Perfect

Thanks Paul

Paul Newcome

Happy to help. 👍️