MATLAB: How to add columns with a certain formula

column formula calculation

Hi guys, I have a cell array with 90 cells. Each cell consits of a table or matrix with 6 columns and one million rows.

Column 1 shows date. Column 2 shows time. Column 3 shows max value. Column 4 shows min value. Column 5 shows closing value. Column 6 shows the average value of column 3/4.

I want to do the following:The first row and column of the cell array should have the following formula: (column 6 – (min value of row 1 to 60 of column 4))/((max value of row 1 to 60 of column 3) – (min value of row 1to 60 of column 4)). I want this formula to be applied for the first 60 rows. At row 61 i want the formula to change to: (column 6 – (min value of row 61 to 120 of column 4))/((max value of row 61 to 120 of column 3) – (min value of the row 61 to 120 of column 4)). I want this formula to jump after every 60 rows until all rows are covered. Thanks

Best Answer

AA - you seem to have a good idea of the algorithm, so it should be straightforward to implement. Suppose myData, a matrix of dimension 1000000x6, is one element in your cell array. Then

 % assume that myData is 2D
 [m,n] = size(myData);
 % size your output column
 calcData = zeros(m,1);
 % iterate in steps of 60
 stepSize = 60;
 for k=1:stepSize:m
    % set the start and end indices for the block of sixty
    startIdx = k;
    endIdx   = min(m,k+stepSize-1);
    % determine the min and max values
    minCol4 = min(myData(startIdx:endIdx,4));
    maxCol3 = max(myData(startIdx:endIdx,3));
    % do the calculation
    calcData(startIdx:endIdx) = (myData(startIdx:endIdx,6) - minCol4)/(maxCol3-minCol4);
 end

Note that we do the min(m,k+stepSize-1); to make sure that we do not set the end index to something beyond the end of the array. Once the code has completed the above loop, you can do whatever you wish with the calcData column (prepend to myData for example), and then move on to the next cell array element.

Related Solutions

MATLAB: Maximum of a row in a matrix

Use max, and some indexing if needed:

A = [...]; % some matrix
N = 3; % the row that you want
max(A(N,:))

MATLAB: Random matrix with maximum values.

You could do this:

w = floor(sqrt(30))
rows = 1 : w
counter = 1;
for k = 1 : length(rows)
	columns(k) = floor(30/rows(k));
	for col = 1 : columns(k)
		fprintf('For %d rows, and %d columns, there are %d elements.\n',...
			rows(k), col, rows(k) * col);
		allMatrices{counter} = rand(rows(k), col);
		counter = counter + 1;
	end
end

You'll get every possible matrix size with 30 or fewer elements in it. And each one will be a cell in the allMatrices cell array. It has to be a cell array rather than a 3-D matrix because the matrices are not all the same size. You'll see in the command window:

w =

rows =

1 2 3 4 5

For 1 rows, and 1 columns, there are 1 elements.

For 1 rows, and 2 columns, there are 2 elements.

For 1 rows, and 3 columns, there are 3 elements.

For 1 rows, and 4 columns, there are 4 elements.

For 1 rows, and 5 columns, there are 5 elements.

For 1 rows, and 6 columns, there are 6 elements.

For 1 rows, and 7 columns, there are 7 elements.

For 1 rows, and 8 columns, there are 8 elements.

For 1 rows, and 9 columns, there are 9 elements.

For 1 rows, and 10 columns, there are 10 elements.

For 1 rows, and 11 columns, there are 11 elements.

For 1 rows, and 12 columns, there are 12 elements.

For 1 rows, and 13 columns, there are 13 elements.

For 1 rows, and 14 columns, there are 14 elements.

For 1 rows, and 15 columns, there are 15 elements.

For 1 rows, and 16 columns, there are 16 elements.

For 1 rows, and 17 columns, there are 17 elements.

For 1 rows, and 18 columns, there are 18 elements.

For 1 rows, and 19 columns, there are 19 elements.

For 1 rows, and 20 columns, there are 20 elements.

For 1 rows, and 21 columns, there are 21 elements.

For 1 rows, and 22 columns, there are 22 elements.

For 1 rows, and 23 columns, there are 23 elements.

For 1 rows, and 24 columns, there are 24 elements.

For 1 rows, and 25 columns, there are 25 elements.

For 1 rows, and 26 columns, there are 26 elements.

For 1 rows, and 27 columns, there are 27 elements.

For 1 rows, and 28 columns, there are 28 elements.

For 1 rows, and 29 columns, there are 29 elements.

For 1 rows, and 30 columns, there are 30 elements.

For 2 rows, and 1 columns, there are 2 elements.

For 2 rows, and 2 columns, there are 4 elements.

For 2 rows, and 3 columns, there are 6 elements.

For 2 rows, and 4 columns, there are 8 elements.

For 2 rows, and 5 columns, there are 10 elements.

For 2 rows, and 6 columns, there are 12 elements.

For 2 rows, and 7 columns, there are 14 elements.

For 2 rows, and 8 columns, there are 16 elements.

For 2 rows, and 9 columns, there are 18 elements.

For 2 rows, and 10 columns, there are 20 elements.

For 2 rows, and 11 columns, there are 22 elements.

For 2 rows, and 12 columns, there are 24 elements.

For 2 rows, and 13 columns, there are 26 elements.

For 2 rows, and 14 columns, there are 28 elements.

For 2 rows, and 15 columns, there are 30 elements.

For 3 rows, and 1 columns, there are 3 elements.

For 3 rows, and 2 columns, there are 6 elements.

For 3 rows, and 3 columns, there are 9 elements.

For 3 rows, and 4 columns, there are 12 elements.

For 3 rows, and 5 columns, there are 15 elements.

For 3 rows, and 6 columns, there are 18 elements.

For 3 rows, and 7 columns, there are 21 elements.

For 3 rows, and 8 columns, there are 24 elements.

For 3 rows, and 9 columns, there are 27 elements.

For 3 rows, and 10 columns, there are 30 elements.

For 4 rows, and 1 columns, there are 4 elements.

For 4 rows, and 2 columns, there are 8 elements.

For 4 rows, and 3 columns, there are 12 elements.

For 4 rows, and 4 columns, there are 16 elements.

For 4 rows, and 5 columns, there are 20 elements.

For 4 rows, and 6 columns, there are 24 elements.

For 4 rows, and 7 columns, there are 28 elements.

For 5 rows, and 1 columns, there are 5 elements.

For 5 rows, and 2 columns, there are 10 elements.

For 5 rows, and 3 columns, there are 15 elements.

For 5 rows, and 4 columns, there are 20 elements.

For 5 rows, and 5 columns, there are 25 elements.

For 5 rows, and 6 columns, there are 30 elements.

Best Answer

Related Solutions

MATLAB: Maximum of a row in a matrix

MATLAB: Random matrix with maximum values.

Related Question