How to calculate the weighted average
83 views (last 30 days)
Show older comments
I have 3 companies. Each one of them has a ME, a Price and a Return (3 rows) with 6 elements each (6 columns). I want to find the Weighted Average of the 3 companies for each year (column). Multiple each element of the 1st Row with each element of the 3rd Row and then divide by the sum of weights (in each column). Then I need to repeat the same task for 10 different sheets.
Thank you
2 Comments
Benjamin Thompson
on 4 Oct 2022
If you are wanting to do this in MATLAB rather than a spreadsheet application like Excel, then attach some of the sample input data.
Accepted Answer
Image Analyst
on 5 Oct 2022
This seems to work fine. It reads in all the sheets of your sample workbook (except the "Results " sheet), and computes the weighted means. You can then do whatever you want with that vector, like write it to the Results worksheet or whatever.
% Demo by Image Analyst
% Initialization Steps.
clc; % Clear the command window.
close all; % Close all figures (except those of imtool.)
clear; % Erase all existing variables. Or clearvars if you want.
workspace; % Make sure the workspace panel is showing.
format long g;
format compact;
fontSize = 18;
markerSize = 40;
ds = spreadsheetDatastore("wa.xlsx")
for k = 1 : numel(ds.Files)
thisFileName = ds.Files{k};
fprintf('Processing file :"%s"\n', thisFileName)
worksheetNames = sheetnames(thisFileName);
for s = 1 : numel(worksheetNames)
thisSheetName = worksheetNames{s};
% Skip the "Results worksheet
if contains(thisSheetName, 'Results','IgnoreCase',true)
continue;
end
thisData = readmatrix(ds.Files{k}, 'Sheet', thisSheetName);
% Crop off first column and first row.
thisData = thisData(2:end, 2:end);
[rows, columns] = size(thisData);
% Find last row of valid numbers before the nans start.
lastRow = find(isnan(thisData(:, 2)), 1, 'first')-1;
% Extract MV
mvWeights = thisData(1 : 3 : lastRow, :);
% Extract returns
AReturn = thisData(3 : 3 : lastRow, :);
% Sum the weights in each column
sumOfWeights = sum(mvWeights, 1); % Sum each column, going down rows.
% Compute weighted sums for this one worksheet in this one workbook:
fprintf(' Weighted Sums for worksheet :"%s"\n', thisSheetName)
weightedAverage = sum(mvWeights .* AReturn, 1) ./ sumOfWeights
end
end
fprintf('Done!\n')
7 Comments
More Answers (1)
Image Analyst
on 4 Oct 2022
Edited: Image Analyst
on 4 Oct 2022
Use readmatrix and tell it what sheet to read in. Then take your data and weights and do an element by element mutliplication and sum:
data = readmatrix(filename, 'sheet', 'whatever');
% Multiple each element of the 1st Row with each element of the 3rd Row
v = data(1, :) .* data(3, :)
% and then divide by the sum of weights (in each column)
weights = I have no idea where to get these. How are you getting the weights????
weightedMean = v ./ sum(weights)
If you have any more questions, then attach your data and code to read it in with the paperclip icon after you read this:
See Also
Products
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!