Method 1 – Use Generic Formula Calculate Conditional Weighted Average with Multiple Conditions in Excel
STEPS:
- Select cell F13.
- Type the following formula in that cell:
=(F5+F6+F9)/(D5+D6+D9)
- Press Enter.
- Get a result like the following image. The value of $1.24 in cell F13 indicates the weighted average only for the food type Fruit.
Method 2 – Utilize Only SUMIFS Function to Enumerate Conditional Weighted Average with Various Conditions.
STEPS:
- Select cell F13.
- Insert the following formula in that cell:
=SUMIFS(F5:F11,C5:C11,"*Fruit*")/SUMIFS(D5:D11,C5:C11,"*Fruit*")
- Hit Enter.
- Get the value of the weighted average for food type Fruit in cell F13.
Method 3 – Calculate Conditional Weighted Average with Multiple Conditions Using Table Format
STEPS:
- Select cell F13.
- Input the following formula in that cell:
=SUMIF(Table2[Food Type],B14,Table2[Total Price])/SUMIF(Table2[Food Type],B14,Table2[Quantity])
- Press Enter.
- See the value of the weighted average in cell F13.
Method 4 – Combine SUMPRODUCT and SUMIF Functions to Calculate Conditional Weighted Average
STEPS:
- Select cell E13.
- Type the following formula in that cell:
=SUMPRODUCT((C5:C11="Fruit")*D5:D11*E5:E11)/SUMIF(C5:C11,"Fruit",D5:D11)
- Press Enter.
- Get the value of the weighted average for fruits in cell E13.
The SUMPRODUCT function returns the product of quantity and average price for the food type Fruit only. The SUMIF function returns the sum of the amount of food type Fruit.
Method 5 – Estimate Weighted Average with Multiple Conditions with SUMPRODUCT, ISNUMBER & SEARCH Functions
STEPS:
- Select cell E13.
- Enter the following formula in that cell:
=SUMPRODUCT((ISNUMBER(SEARCH("Fruit",C5:C11))*(D5:D11)*(E5:E11)))/SUMPRODUCT((ISNUMBER(SEARCH("Fruit",C5:C11))*(D5:D11)))
- Hit Enter.
- See the result in the following image. The above formula returns the value of the weighted average for food type Fruit in cell E13.
How Does the Formula Work?
- SEARCH(“Fruit”,C5:C11): the SEARCH function searches for the string Fruit within cell range C5 to C11.
- ISNUMBER(SEARCH(“Fruit”,C5:C11)): the ISNUMBER function checks whether the value we are searching for is a number or not.
- SUMPRODUCT((ISNUMBER(SEARCH(“Fruit”,C5:C11))*(D5:D11)*(E5:E11))): This part returns the product of the specified ranges for the food type Fruit.
- SUMPRODUCT((ISNUMBER(SEARCH(“Fruit”,C5:C11))*(D5:D11))): the SUMPRODUCT function returns the sum of quantity for the food type Fruit.
- SUMPRODUCT((ISNUMBER(SEARCH(“Fruit”,C5:C11))*(D5:D11)*(E5:E11)))/SUMPRODUCT((ISNUMBER(SEARCH(“Fruit”,C5:C11))*(D5:D11))): This part calculates the weighted average for the food type Fruit.
Download Practice Workbook
You can download the practice workbook from here.
Related Articles
<< Go Back to Weighted Average Excel | How to Calculate Average in Excel | How to Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
