Microsoft Office Tutorials and References
In Depth Information
Summarizing data that meets specific conditions
In this exercise, you’ll create a conditional formula that displays a message if a condition
is true, find the average of worksheet values that meet one criterion, and find the sum of
worksheet values that meet two criteria.
SET UP You need the PackagingCosts workbook located in the Chapter03 practice file
folder to complete this exercise. Open the workbook, and then follow the steps.
In cell G3 , enter the formula =IF(F3>=35000, “Request discount”, “No discount
available”) , and press Enter to create the formula, which displays Request discount
if the value in cell F3 is at least 35,000 and displays No discount available if not. The
value Request discount appears in cell G3 .
Click cell G3 , and drag the ill handle down until it covers cell G14 . Excel copies
the formula in cell G3 to cells G4:G14 , adjusting the formula to reflect the cells’
addresses. The results of the copied formulas appear in cells G4:G14 .
In cell I3 , enter the formula =AVERAGEIF(C3:C14, “=Box”, F3:F14) and press Enter
to display the average cost per category of boxes, $46,102.50 , in cell I3 .
In cell I6 , enter =SUMIFS(F3:F14, C3:C14, “=Envelope”, E3:E14,
“=International”) , and press Enter to display the value $45,753.00 , which represents
the total cost of all envelopes used for international shipments, in cell I6 .
CLEAN UP Close the PackagingCosts workbook, saving your changes if you want to.