WEIGHTED_AVERAGE 

Value	Weight
10	0.1
15	0.1
5	1
Result	6.25

Scenario: You want to create a custom Google Sheets formula that calculates the weighted average of values in a specified range, where each value is multiplied by its corresponding weight.

Data Table:

	A	B	C
1	Value	Weight
2	10	0.3
3	15	0.5
4	5	0.2
5
6	Result

Step 1: Set Up the Spreadsheet

• Open a new Google Sheets document.
• Enter the data table as shown above in columns A and B.
• Leave cells C2 and C6 empty for now.

Step 2: Write the Google Apps Script Code

• Click on “Extensions” in the top menu, then select “Apps Script”.
• Replace the default code with the following script:

// Custom formula to calculate the weighted average of values

function WEIGHTED_AVERAGE(values,weights){

let sum = 0;

let totalWeight = 0;

for(let i=0;i<values.length;i++){

sum += values[i] * weights[i];

totalWeight += weights[i];

}

return sum/totalWeight;

}

Step 3: Use the Custom Formula in Google Sheets

• Go back to your Google Sheets document.
• In cell C6, enter the following formula:

=WEIGHTED_AVERAGE(A2:A4, B2:B4)

Explanation:

• The WEIGHTED_AVERAGE function takes two parameters: values and weights. Both parameters represent ranges of cells that hold the values and weights, respectively.
• Inside the function, we initialize the sum variable to store the cumulative product of each value multiplied by its corresponding weight, and the totalWeight variable to store the sum of weights.
• The for loop iterates through each value in the values range. For each value, it multiplies the value by its corresponding weight from the weights range and adds it to the sum. Additionally, it adds the weight to the totalWeight.
• After looping through all values, the function returns the weighted average by dividing the sum by the totalWeight.

Step 4: Testing the Custom Formula

• Enter the values and weights in columns A and B, respectively.
• Use the custom formula in cell C6 to calculate the weighted average.

For example, if you input the values 10, 15, and 5 in cells A2:A4 and the weights 0.3, 0.5, and 0.2 in cells B2:B4, the calculated weighted average in cell C6 should be approximately 11.67.

Remember that you need to enable the “Google Apps Script” extension and use the exact function name (WEIGHTED_AVERAGE) in your formula.

The provided code and instructions describe how to create a custom Google Sheets formula using Google Apps Script to calculate the weighted average of a range of values. The formula takes two parameters: the range of values and the corresponding range of weights. The weighted average is calculated by multiplying each value with its respective weight, summing up the products, and then dividing by the total sum of weights.

Here’s a step-by-step breakdown of the process:

1. Set Up the Spreadsheet:
   • Open a new Google Sheets document.
   • Enter the data table with values and weights in columns A and B.
   • Leave cells C2 and C6 empty for now.
2. Write the Google Apps Script Code:
   • Click on “Extensions” in the top menu, then select “Apps Script.”
   • Replace the default code with the provided script:

// Custom formula to calculate the weighted average of values function WEIGHTED_AVERAGE(values, weights) { let sum = 0; let totalWeight = 0; for (let i = 0; i < values.length; i++) { sum += values[i] * weights[i]; totalWeight += weights[i]; } return sum / totalWeight; }

3. Use the Custom Formula in Google Sheets:
   • Go back to your Google Sheets document.
   • In cell C6, enter the following formula:

lessCopy code

=WEIGHTED_AVERAGE(A2:A4, B2:B4)

• This formula uses the custom function WEIGHTED_AVERAGE and provides the ranges of values (A2:A4) and weights (B2:B4) as parameters.

4. Testing the Custom Formula:
   • Enter values and weights in columns A and B, respectively.
   • The custom formula in cell C6 will calculate the weighted average based on the provided values and weights.
   • For example, if you input the values 10, 15, and 5 in cells A2:A4 and the weights 0.3, 0.5, and 0.2 in cells B2:B4, the calculated weighted average in cell C6 should be approximately 11.67.

Remember that you need to enable the “Google Apps Script” extension and use the exact function name (WEIGHTED_AVERAGE) in your formula.

This custom formula can be quite useful when you need to calculate weighted averages for various purposes, such as analyzing grades, financial data, or any scenario where values are assigned different weights.