» Calculating an Average, Only Including Prices on Which There Was no Discount

CATEGORY - Excel Formula and Function VERSION - All Microsoft Excel Versions

Problem:

Calculating the average of the prices from column B, but only including those on which there was no discount (as shown in column A).

Solution 1:

Use the AVERAGE and IF functions as shown in the following Array Formula:
{=AVERAGE(IF(A2:A6="No",B2:B6))}

To apply Array formula:
Select the cell, press and simultaneously press .

Solution 2:

Use the SUMIF and COUNTIF functions as shown in the following formula:
=SUMIF(A2:A6,"No",B2:B6)/COUNTIF(A2:A6,"No")

Screenshot // Calculating an Average, Only Including Prices on Which There Was no Discount