LARGE & AVERAGEIFS Functions With Logical Operators

Welcome to Excel Jet Consult blog post. In this post, we will learn how to combine LARGE & AVERAGEIFS Functions with Logical Operators. Let’s get started

LARGE Function returns the k-th largest value in a data set

LARGE Syntax                    =LARGE(array, k)

AVERAGEIFS Function returns the average for the cells specified by a given set of conditions or criteria

AVERAGEIFS Syntax        =AVERAGEIFS(average_range, criteria_range1, criteria1, …)

In the caption below, we want to find the Average Total Sales for Product Category that does not equal to Beverage

In cell C2, execute the formula: =LARGE(AVERAGEIFS(H5:H204,E5:E204,”<>Beverages”),1)

As seen in the caption below, the Average Total Sales for Product Category that does not equal to Beverages is ₦1,957.16

To verify this, we will filter out Beverages from the Product Category.

From the caption above we can see that Average seen in the Status Bar tallied with the value in cell C2. That is how to use LARGE combined with AVERAGEIFS functions with logical operators