Excel Sheet Sorting and Data Analysis

I have an excel sheet with about 100,000 rows and 30 columns. I am trying to get averages of certain columns for rows that fit a certain description. For example, the excel sheet has names, grades, and favorite colors, and I am trying to find out the average grade of only the people that have "blue" listed in the favorite colors column.

I normally wouldn't use Matlab for this, but given that the spreadsheet is so big, I figured it would be beneficial to use it rather than sorting on Excel.

Any advise is appreciated!! I have used Matlab very sparingly in the past so I am still a beginner. Youtube videos/web links/reddit posts/etc. are appreciated too!

Answer 1

[Scott MacKenzie]

Assuming the data for the names, grades, and favorite colors are in columns 1, 2, and 3, respectively, then the following code outputs the mean of the grades for the people whose favorite color is blue:

T = readtable('testdata.xlsx');
blueLogical = strcmp(T{:,3}, 'blue');
blueGrades = T{blueLogical,2}; 
mean(blueGrades) 

[Carolina Benavides]

Follow up question. Let's say that instead of wanting to get the averages of the scores, I want to get a count of the number of students whose favorite color is blue. Additionally, I want to know the percentage of the number of students whose favorite color is blue out of all the students. Then I want to find out of the percentage of the number of students whose favorite color is blue out of all the students whose favorite color is blue and are in 10th grade.

[Scott MacKenzie]

These questions are all fairly easy to answer in MATLAB. For the first two...

% count of number of students whose favorite color is blue
sum(blueLogical)

% percentage of students whose farorite color is blue
sum(blueLogical) / height(T) * 100

For your last question, you didn't mention anything about the grade levels of students in your original question. But, if we assume column 4 contains the grade the student is in, then...

% percentage of all students in 10th grade and blue as favorite color 
blueTenLogical = blueLogical & (T{:,4} == 10);
sum(blueTenLogical) / height(T) * 100