# Calculate average between min and max numbers in column 3 depending on values in column 2, for each value in column 1

4 views (last 30 days)
Neuro on 19 Jan 2022
Edited: _ on 20 Jan 2022
Hello. I was wondering if someone could help me with this:
Let's say you have a 2000 x 3 matrix, where all the elements are integers. In column 1, the values can be between 1 and 16. How can we select one element in column 1 (e.g. 7), then for those rows only, select the rows with the highest 5 values in column 2, and for these rows only, select their elements in column 3 and calculate the average between the min and max values (output).
Thanks!
##### 2 CommentsShowHide 1 older comment
_ on 20 Jan 2022
@Neuro FYI, the accepted answer here selects the 5 rows with the highest value in column 2, whereas the accepted answer in another question you posted selects all rows whose value in column 2 is among the 5 highest unique values in column 2. You should be aware of this distinction because it seems likely that these questions are related.
To illustrate the distinction:
X = [1 2 3 4 5 5 5 6 6 6 6];
maxk(X,5) % 5 highest values
ans = 1×5
6 6 6 6 5
uX = unique(X);
X(X >= uX(end-4)) % among the 5 highest unique values
ans = 1×10
2 3 4 5 5 5 6 6 6 6

Matt J on 19 Jan 2022
Edited: Matt J on 19 Jan 2022
A=yourMatrix;
idx=A(:,1)==7;
B=A(idx,:);
[~,idx]=maxk(B(:,2),5);
C=B(idx,3);
result=0.5*(max(C)+min(C))
Neuro on 20 Jan 2022
Thanks! That's exactly what I needed.

Image Analyst on 19 Jan 2022
Just take it one step at a time. Try this.
% Let's say you have a 2000 x 3 matrix, where all the elements are integers.
% In column 1, the values can be between 1 and 16. How can we select one element in column 1 (e.g. 7),
% then for those rows only, select the rows with the highest 5 values in column 2,
% and for these rows only, select their elements in column 3 and
% calculate the average between the min and max values (output).
m = randi([1, 16], 2000, 3);
% Find rows with column 1 = 7
rows1Equals7 = m(:, 1) == 7;
% Get values from column 2 in those rows
col2 = m(rows1Equals7, 2)
col2 = 109×1
7 11 6 3 5 12 7 12 8 1
% Select the rows with the highest 5 values
[highValues, rowsWithHighValues] = maxk(col2, 5)
highValues = 5×1
16 16 16 16 16
rowsWithHighValues = 5×1
33 35 41 44 46
% Get the values from column 3 in those 5 rows.
col3 = m(rowsWithHighValues, 3)
col3 = 5×1
11 4 5 4 15
% Get the average "between" the max and min. Not sure what "between" means
% so I'm taking the average of the min and max.
max3a = mean([min(col3), max(col3)])
max3a = 9.5000
% Alternative: Get the average of ALL 5 values
max3b = mean(col3)
max3b = 7.8000

_ on 19 Jan 2022
matrix = randi([1 16],2000,3);
search_number = 7;
idx_1 = matrix(:,1) == search_number;
col2_vals = unique(matrix(idx_1,2));
idx_2 = idx_1 & matrix(:,2) >= col2_vals(end-4);
col3_min = min(matrix(idx_2,3));
col3_max = max(matrix(idx_2,3));
col3_avg = (col3_min+col3_max)/2;
disp(col3_min);
1
disp(col3_max);
16
disp(col3_avg);
8.5000

R2021b

### Community Treasure Hunt

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

Start Hunting!