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:
- Solver in Excel or Sheets
- An approach similar to linear optimization function as in this StackOverflow discussion
- 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
You can use Google Apps Script and import nerdamer library. See the setup below:
Setup:
NOTE: Follow the order or you will have errors
Sample data:
Output:
Note:
EDIT:
LinearOptimizationService
. See sample below:Output:
Note:
Reference: