How to display a list from excel using different values

I have an excel spreadsheet with 3 columns of information that I am using: A is a list of country names, B is a % value, and C is a total value. In matlab I imported the information, assigned each column a variable name, and created a table out of it. I then created a new column to the table within matlab by multiplying the values of B by C. Finally I used if/elseif statements to break up the values of this new column to separate them into 4 value ranges (NaN, 0-299, 300-2499, and 2500+)

My question is, how can I have matlab display the certain country names from column A, associated with the specific range of values from the newest column (from highest value to lowest)?

[Scott MacKenzie]

Why don't you post your spreadsheet, or a reasonable subset of it. I can picture the solution, but I'm not inclined to create a mock data set to work through the details.

[Kyle]

Sorry about that. This is my first time posting a question here and thought using the A, B, and C for the columns might make it simpler to answer.

But I used columns B, C, and D from the excel spreadsheet to make a table in matlab. Used matlab to create a new column with values for C*D. Im wanting matlab to identify the values of this newest column based on a range of values I give it, but rather than displaying those values that fall within the range, I am wanting it to display only the country names that correspond to those values within a certain range (determined by a user input) from highest to lowest.

Answer 1

[Scott MacKenzie]

Kyle's value ranges are not quartiles. Here's what I put together, starting with Walter's code to read the table and add the Usable column:

filename = 'https://www.mathworks.com/matlabcentral/answers/uploaded_files/595985/New_Percent_Land_Degraded_(Revised%20Country%20List_59).xlsx';
opt = detectImportOptions(filename, 'preservevariablenames', true);
opt = setvaropts(opt, 3, 'type', 'double');
T = readtable(filename, opt);
T.Usable = T{:,3} .* T{:,4};

% first deal with the NaN entries
fprintf('NaN\n');
fprintf('   %s\n', T.GeoAreaName{isnan(T.Usable)});

% next deal with the remaining categories, as per your value ranges
yourEdges = [0, 300, 2500, Inf];
category = {'0 to < 300', '300 to < 2500', '2500+'};
for i=1:length(category)
    fprintf('\n%s\n', category{i});
    fprintf('   %s\n', T.GeoAreaName{T.Usable >= yourEdges(i) & T.Usable < yourEdges(i+1)});
end

This yields what Kyle is looking for (I think):

NaN

Åland Islands

Andorra

Austria

Bermuda

Bulgaria

Canada

Channel Islands

Croatia

Denmark

Estonia

Faroe Islands

Germany

Gibraltar

Greenland

Guernsey

Holy See

Iceland

Ireland

Isle of Man

Jersey

Liechtenstein

Malta

Monaco

Netherlands

North Macedonia

Norway

Saint Pierre and Miquelon

San Marino

Svalbard and Jan Mayen Islands

Sweden

Switzerland

United Kingdom of Great Britain and Northern Ireland

United States of America

0 to < 300

300 to < 2500

Luxembourg

2500+

Belarus

Finland

Belgium

France

Hungary

Italy

Latvia

Greece

Spain

Romania

Ukraine

Republic of Moldova

Lithuania

Portugal

Bosnia and Herzegovina

Slovakia

Poland

Slovenia

Czechia

Montenegro

Russian Federation

Serbia

Albania

[Kyle]

This wasn't exactly what I was looking for, but that is probably my own fault for not providing you guys with enough information from the start as to where I was at in the process, and what I couldn't figure out. Regardless though, the codes both of you provided introduced me to commands I did not know about, and I was able to adapt them to fit my needs. Thank you both for taking the time to reply to this, it's much appreciated.

Answer 2

[Walter Roberson]

filename = 'https://www.mathworks.com/matlabcentral/answers/uploaded_files/595985/New_Percent_Land_Degraded_(Revised%20Country%20List_59).xlsx';
opt = detectImportOptions(filename, 'preservevariablenames', true);
opt = setvaropts(opt, 3, 'type', 'double');
T = readtable(filename, opt);
T.Usable = T{:,3} .* T{:,4};
[counts, edges, bins] = histcounts(T.Usable, 4);
for quarter = 1 : 4
  fprintf('\nQuartile %d: %g - %g\n', quarter, edges(quarter:quarter+1));
  disp(categorical(T.GeoAreaName(bins == quarter)));
end

Quartile 1: 0 - 2.5e+06

     Belarus 
     Finland 
     Belgium 
     France 
     Hungary 
     Italy 
     Latvia 
     Greece 
     Spain 
     Romania 
     Ukraine 
     Republic of Moldova 
     Lithuania 
     Portugal 
     Bosnia and Herzegovina 
     Luxembourg 
     Slovakia 
     Poland 
     Slovenia 
     Czechia 
     Montenegro 
     Serbia 
     Albania 

Quartile 2: 2.5e+06 - 5e+06

Quartile 3: 5e+06 - 7.5e+06

Quartile 4: 7.5e+06 - 1e+07

     Russian Federation 

[Walter Roberson]

Notice the fundamental mechanic here: determine which "bin" applies each data item, using histcounts (or using discretize). Then you can compare the bin number against a particular bin number to create a logical mask that matches only the entries that had that range of values. You can use that mask to extract the appropriate subset of values, such as T.Usable(bins == quarter) and you can extract the country names using T.GeoAreaName(bins == quarter). Do you know how to sort two items with respect to each other? See the second output of sort()