Figure out the weights of a weighted average in Google Sheets or Excel

Question

Scores in this datset in sheets are calculated using a weighted average of 4 variables in columns B:E. The weighted average is the same for each line.

Given several lines of scores and variables, how can the weights be discovered in excel or sheets?

Average weight function in sheets is:

AVERAGE.WEIGHTED(B2,a,C2,b,D2,c,E2,d)

Possible approaches:

1. Solver in Excel or Sheets
2. An approach similar to linear optimization function as in this StackOverflow discussion
3. Essentially, the solution I am thinking of automates a process of guessing (through linear regression or another mechanism)

I attempted to set this up using Solver in Excel, but I am not sure that it can be set up to solve this or if my lack of familiarity with solver which is also available in sheets contributed.

Thank you in advance for any help. The dataset is shared in this sheet

Answer 1

You can use Google Apps Script and import nerdamer library. See the setup below:

Setup:

1. Create separate gs files containing these libraries.

• https://cdn.jsdelivr.net/npm/nerdamer@latest/nerdamer.core.js
• https://cdn.jsdelivr.net/npm/nerdamer@latest/Algebra.js
• https://cdn.jsdelivr.net/npm/nerdamer@latest/Calculus.js
• https://cdn.jsdelivr.net/npm/nerdamer@latest/Solve.js

NOTE: Follow the order or you will have errors

2. Use the script below:

function solveWeights() {
 
  // Get data, process, then outputs on the actual column header.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const vars = sheet.getLastColumn() - 1;

  let data = sheet.getRange(1, 1, vars + 1, vars + 1).getValues();
  let headers = data.shift();

  let solution = [nerdamer.solveEquations(data.map(row => {
    return `${row[0]}=${row.slice(1).map((x, i) => x + headers[i + 1]).join('+')}`;
  })).toString().split(',').filter((_, i) => i % 2 == 1).map((x, i) => `${headers[i + 1]} = ${x}`)]

  sheet.getRange(1, 2, solution.length, solution[0].length).setValues(solution);
}

Sample data:

Output:

Note:

• You can add offset to your output range so that it won't overwrite your columns.
• The preparation of multiple libraries slows the execution so it might take a while, but should take around 5 seconds to be done (using 4 column/variable data)

EDIT:

• If you don't want to use external libraries, you can use LinearOptimizationService. See sample below:

function solveWeight() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const vars = sheet.getLastColumn() - 1;

  // fetch N rows based on how many variables are present
  const data = sheet.getRange(1, 1, vars + 1, vars + 1).getValues();
  let headers = data.shift();
  headers = headers.slice(1);

  let engine = LinearOptimizationService.createEngine();
  // add variable
  headers.forEach(header => {
    // set the range of the weights' possible values (e.g. 0 - 100)
    engine.addVariable(header, 0, 100);
  });

  // create constraint
  data.forEach(row => {
    let sum = row.shift();
    let constraint = engine.addConstraint(sum, sum);
    // set all values as coefficients
    row.forEach((value, index) => {
      constraint.setCoefficient(headers[index], value);
    });
  });

  // get lowest possible values
  engine.setMinimization();

  let solution = engine.solve();
  if (!solution.isValid())
    Logger.log('No solution: ' + solution.getStatus());
  else 
    headers.forEach(header => {
      // workaround due to rounding issue of javascript
      console.log(header + ' value is: ' + solution.getVariableValue(header).toFixed(2))
    });
}

Output:

Note:

• Downside to this approach is that you need to declare what the range of the weights, and other variables such as the coefficient per weight and etc. The above code assumes all weights are between 0 - 100.
• Code on Linear Optimization service only logs the data, you can modify this to be a custom function instead or just write it directly to the sheet.

Reference:

• Linear Optimization Service