How to calculate the weighted average

32 views (last 30 days)
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
ALEXANDRA
ALEXANDRA on 5 Oct 2022
Hello,
Thank you for answering.
Attached you can find my file along with the calculations.
I know how to do the calculations in Excel. This is not a problem. However, in this example I have very few data. For my project, I have hundreds of data and I cannot do all these calculations one by one and I am not familiar with Macros in Excel.
I am a little bit familiar with Matlab thus why I am trying to do it in Matlab.
Also, for my project, I do not have only one Sheet but my file includes several sheets (in my example I include only 2) and at the end I need to combine all my results in one Row or Column

Sign in to comment.

Accepted Answer

Image Analyst
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
ALEXANDRA
ALEXANDRA on 19 Oct 2022
Hello,
I have some spreadsheets that look like the attached ('WA1'). The attached is just an example, in reality I have hundreds of data.
I have been trying to adapt the code by changing the lines 32-35 and say to it to continue calculating up to the end of ‘D’ in my example, but I have not achieved to do it and the calculations stop after ‘A’ because OK it stops when it finds the 1st empty line.
Is it possible something like that?
Thank you very much!

Sign in to comment.

More Answers (1)

Image Analyst
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:
  1 Comment
ALEXANDRA
ALEXANDRA on 5 Oct 2022
Hello,
Thank you for your answer.
I cannot use the command readmatrix because my original sheets includes names and dates as well.
Attached you can find my file (just an example) along with the calculations.
I know how to do the calculations in Excel. This is not a problem. However, in this example I have very few data. For my project, I have hundreds of data and I cannot do all these calculations one by one and I am not familiar with Macros in Excel.
I am a little bit familiar with Matlab thus why I am trying to do it in Matlab.
Also, for my project, I do not have only one Sheet but my file includes several sheets (in my example I include only 2) and at the end I need to combine all my results in one Row or Column
Thank you

Sign in to comment.

Products

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!