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

Scores in this datset

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

Average weight function in

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