Table of Contents
A LARGE IF formula in Google Sheets allows users to specify a condition and return the nth largest value from a range of data that meets that condition. This can be useful for data analysis and decision making. To use a LARGE IF formula, users must first identify the condition and the range of data they want to analyze. They can then use the formula to specify the condition and the desired nth largest value, which will be returned by the formula. This can be done by nesting the LARGE and IF functions together in the formula. Overall, using a LARGE IF formula in Google Sheets can help users efficiently and accurately analyze data based on specific conditions.
Use a LARGE IF Formula in Google Sheets
You can use the following methods to create a LARGE IF formula in Google Sheets:
Method 1: LARGE IF with One Criteria
=ArrayFormula(LARGE(IF(A2:A11="value",C2:C11),2))This formula finds the second largest value in column C where the value in column A is equal to “value.”
Method 2: LARGE IF with Multiple Criteria
=ArrayFormula(LARGE(IF((A2:A11="value1")*(B2:B11="value2")=1,C2:C11),5))
This formula finds the fifth largest value in column C where the value in column A is equal to “value1” and the value in column B is equal to “value2.”
The following examples show how to use each method with the following dataset in in Google Sheets:
Example 1: LARGE IF with One Criteria
We can use the following formula to calculate the second largest value in the Points column only for the rows where the Team column is equal to “Spurs”:
=ArrayFormula(LARGE(IF(A2:A11="Spurs",C2:C11),2))The following screenshot shows how to use this syntax in practice:
We can see that the second largest Points value among rows where Team is equal to “Spurs” is 26.
Example 2: LARGE IF with Multiple Criteria
We can use the following formula to calculate the second largest value in the Points column only for the rows where the Team column is equal to “Spurs” and the Position column is equal to “Guard”:
=ArrayFormula(LARGE(IF((A2:A11="Spurs")*(B2:B11="Guard")=1,C2:C11),2))The following screenshot shows how to use this syntax in practice:
We can see that the second largest Points value among rows where Team is equal to “Spurs” and Position is equal to “Guard” is 24.
Note: You can find the complete documentation for the LARGE function in Google Sheets .
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
Cite this article
stats writer (2024). How can I use a LARGE IF formula in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-a-large-if-formula-in-google-sheets/
stats writer. "How can I use a LARGE IF formula in Google Sheets?." PSYCHOLOGICAL SCALES, 30 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-a-large-if-formula-in-google-sheets/.
stats writer. "How can I use a LARGE IF formula in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-a-large-if-formula-in-google-sheets/.
stats writer (2024) 'How can I use a LARGE IF formula in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-a-large-if-formula-in-google-sheets/.
[1] stats writer, "How can I use a LARGE IF formula in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use a LARGE IF formula in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.