How to calculate the weighted average
76 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
ALEXANDRA
el 4 de Oct. de 2022
Comentada: ALEXANDRA
el 19 de Oct. de 2022
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 comentarios
Benjamin Thompson
el 4 de Oct. de 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.
Respuesta aceptada
Image Analyst
el 5 de Oct. de 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 comentarios
Más respuestas (1)
Image Analyst
el 4 de Oct. de 2022
Editada: Image Analyst
el 4 de Oct. de 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:
Ver también
Productos
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!