Create a blank workbook or sheet.

Select the following sample data.

 
Selecting sample data in Help

Press ⌘+C.

In the sheet, select cell A1, and then press ⌘+V.

Select cells A2 through A7 (the values in the Unit Quality column).

On the status barA horizontal area at the bottom of the document window that shows information about the current state of what you are viewing in the window, and any other contextual information., click the area to the left of the SCRL (scroll lock) indicator (the area might contain the text Sum=49), and then click Average.

The result is 8.166666667.

Select cell A8, which is the cell to the left of the cell that contains the text "Average unit quality."

Click the arrow next to AutoSum , click Average, and then press RETURN.

The result is 8.166666667, which is the average quality rating for all units.

Select cell A9, which is the cell to the left of the cell that contains the text "Average price per unit."

On the View menu, click Formula Builder.

In the Formula Builder list, double-click SUMPRODUCT.

Under Arguments, click the box next to array1, and then on the sheet, select cells B2 through B7 (the values in the Price Per Unit column).

Under Arguments, click the box next to array2, and then on the sheet, select cells C2 through C7 (the values in the Number of Units Ordered column).

In the formula bar, click to the right of the closing parenthesis for the formula, and then type /

If you don't see the formula bar, on the View menu, click Formula Bar.

In the Formula Builder list, double-click SUM.

Under Arguments, select the range in the number1 box, press DELETE, and then on the sheet, select cells C2 through C7 (the values in the Number of Units column).

Press RETURN.

This formula divides the total cost of all the orders by the total number of units ordered, which results in a weighted price per unit average of 29.38297872.

Select cell A10, which is the cell to the left of the cell that contains the text "Average quality for all units rated above 5."

On the View menu, click Formula Builder.

In the Formula Builder list, double-click AVERAGEIF.

Under Arguments, click the box next to range, and then on the sheet, select cells A2 through A7 (the values in the Price Per Unit column).

Under Arguments, click the box next to criteria, and then type ">5".

Press RETURN.

The formula calculates the average unit quality for all units with a rating greater than 5, which excludes the value in cell A7 from the calculation, and results in an average unit quality of 8.8.