CP2 Excel Weighted Average

Instructions on how to calculate the weighted average in Excel.

In this example the SUMPRODUCT and SUM function will be used in Excel.

The weighted average is useful to calculate the overall grade for a class.

Computer with Microsoft Excel

Start by first labeling the excel sheet.

The top row being class, grade, and weight

The first column being the specific assignments in this case two homeworks, two quizzes, two assignments, two test, and a final exam.

Now that you have labeled the excel sheet you can start adding your assignment grades, and the weight of each assignment.

Add three labels underneath your data that has been entered, the three cells should be labeled as SUMPRODUCT, SUM, and weighted average. This is where the calculations will be displayed.

To calculate the SUMPRODUCT, first select the cell next to where SUMPRODUCT has been labled this is where the calculation will be displayed. After you have selected the cell next to the SUMPRODUCT label, in this case C12, click the formulas tab located in the top bar, then click Math & Trig, scroll down the drop down menu until SUMPRODUCT is displayed and click on it.

Once selecting SUMPRODUCT, the Function Arguments window will open.

The first step to calculating the SUMPRODUCT is selecting the grades. Click the Array1 box, then select the first grade in the column labeled grade, and drag your mouse until the final grade in the list is selected. It should appear as the first cell separated by a colon and the last cell, in this example it is B2:B10.

For Array2 the same step needed to be repeated except besides selecting the grade, this time the selected cells are the weight. Start by clicking Array2 and selecting the first cell under weigh then dragging the cell until the last weigh cell. This will be displayed as the first cell with a colon then the last cell under weight.

Array1 is labeled with a a "G" for grade and Array1 is labeled with a "W" for weight.

After both are selected. Click "OK" to complete the calculation.

The results of the SUMPRODUCT should be displayed in the cell you selected.

Now the SUM needs to be calculated, similarly to calculating SUMPRODUCT, select the formulas button at the top, then Math & Trig, then scroll down and click "SUM".

Now that "SUM" has been selected, the Function Arguments window pops up. For Number 1, the weight cells need to be selected, this is done the same way Array1 and Array2 for SUMPRODUCt were selected. Click the first cell underneath weight and drag it until the last weigh cell. Once the weight is selected, click "OK" to finish the calculation.

After clicking "OK" the sum of the weigh will be displayed, if the information was entered correctly the sum should equal 100.

To calculate the weighted average, the SUMPRODUCT need to be divided by the SUM. To do this fist enter an equal sign(=) into the cell next to the weighted average label. After an equal sign has been entered select the SUMPRODUCT value by clicking it.

Once the SUMPRODUCT value is selected, it needs to be divided by the SUM value. To do this, first type a forward slash (/) then select the SUM value. Once this is complete, you can press enter to show the result.

Once the SUMPRODUCT has been divided by the SUM, the weighted average will be shown.