Using Excel files in function

I have a spreadsheet of list of cities and distances between them such as

New York Washington DC Los Angeles

New York 0 500 . 1000

Washington DC 500 0 . 1500

Los Angeles 1000 1500 0

how do i create a function which takes 2 cities as input and gives the distance as output, given that invalid city names give 0 as distance?

I tried a few things but honestly I don't know how to proceed with this problem.

[Bob Thompson]

Could you expand a little on what things you tried, and why they didn't work? That will help us give you a more clear answer, rather than something super general.

[Perturabo]

I tried reading the excel raw data into a matrix and returning the element at m,n where m and n are the cities. But it doesn't work and it returns the whole cell array itself. But honestly I didn't understand file I/O in MATLAB enough to handle functioning with excel.

Answer 1

[Bob Thompson]

This is a quick first cut, so you will likely need to do some fine tuning of your own. I am assuming that your city distance data is one large square array, with city names in first row and first column.

function [distance] = city_distance(city1,city2);

% Read in the city data, keeping all data in cells
[~,~,data] = xlsread('mycityfile.xlsx');

% Check for first city
c1 = cellfun(@(x) strcmp(x,city1),data(:,1)); % Edit
if c1 == 0;                                   % Edit
    error('No match was found for the first city.')
end

% Check for the second city
c2 = cellfun(@(x) strcmp(x,city2),data(1,:)); % Edit
if c2 == 0;                                   % Edit
    error('No match was found for the second city.')
end

% Distance determination
distance = data{c1,c2};
end

[Perturabo]

I more or less understand the code, but this doesn't give error for invalid cities. The cities in the spreadsheet are all US cities, but when I run it with input London, it still provides an answer. Also, it is providing answers that aren't correct, or even in nearby cells so that I can use

distance = data{c1+something,c2+something}

Also, the first cell in the spreadsheet is empty if that is the reason for error.

Thanks for your help, in any case.

[Bob Thompson]

How comprehensive is the list of cities? There is a London, Ohio, but I imagine it's not particularly large.

What do you mean by 'answers that aren't correct'? Is it picking the wrong city, or not locating the correct distance cell?

What 'error' are you receiving? Can you post the exact error message?

[Bob Thompson]

Please note edits to my answer, regarding finding the names.

[Perturabo]

The function isn't giving any error. But the values are not as expected. For example, it gives cell 530 for Seattle and Miami, while the actual distance in that cell is 3700. And as for the size of the spreadsheet, it is 340×340.

And since it is such a huge cell, I can't calculate how far off the output is from the targeted cell.

[Bob Thompson]

Can you post your spread sheet?

[Perturabo]

Here it is

[Bob Thompson]

Using your excel sheet with the editted code above yeilds the correct answers for every case I tested. Have you copied my edits in yet? I did change the finding function to increase its accuracy.

[Perturabo]

Works perfectly. Thank you very much.

the problem was that I didn't know how to do this:

c1 = cellfun(@(x) strcmp(x,city1),data(:,1));

[Ajai Kannan]

Hello!

I was looking for a way to solve the same problem. This question is from a coursera course and the thing is we were not taught about the cellfun() function and when i tried the help command, I could understand nothing. Can you please explain how that works?

Meanwhile, the following was the code I tried to solve and it gave me errors in the if statement.

function distance = get_distance(a,b)
[~,text,raw] = xlsread('Distances.xlsx');
for i = 2 : size(text,1)
    if text{i,1} == a
       for j = 1 : size(text,2)
           if text{2,j} == b
               distance = raw{i,j};
           else
               distance = -1;
           end
       end
    else
        distance = -1;
    end
end

[Priyamvada Shankar]

Have you got that where you went wrong,if yes then help me also... I'm too stuck in this question.

[Mahesh Sharma]

hey

try this one , this works.

function distance = get_distance(A,B)
[~,~,raw] = xlsread('Distances.xlsx');
distance = -1;
for i = 2 : size(raw,1)
   if strcmp(A,raw{1,i})
       for j = 2 : size(raw,2)  
           if strcmp(B,raw{j,1})
           distance = raw{j,i};
           return
           end
       end
   else
      distance = -1; 
    end
end
end

Answer 2

[Ana Guerreiro]

Hi Bob Nbob

Your code does not work for "non-existent city" that needs to be equal to -1 (Distance= -1).

Can you help me, showing how to do it, please?

Best regards