Custom excel formulas/functions in matlab

I used other's matlab coding to generate data's of some tracked particles and I would like to calculate the mean square displacement. The following are the excel data sheet.

and I would like to use matlab for further handling the datas, in column K, I would like to input the excel formulas of =((H1)^2+(I1)^2+(J1)^2)/3 and loop til the end for each row respectively. and for column L, I would like to input the excel formulas of =max(column F). However, I am new to matlab and unable to write the code for the excel functions.

I understand that i need to use xlsread and xlswrite. So importing the excel file should be like this, right?

clear

dataset = xlsread("12.35mW-cm22_gray.xlsx",'5')

Then write code for the excel formulas.

Then use xlswrite to read a new excel file.

Could someone please help? I would appreciate your help a lot. Thank you.

Answer 1

[Amritesh]

You can import your .xls file in variable dataset, then make changes to it and finally export that in .xls file.

dataset = xlsread("12.35mW-cm22_gray.xlsx",'5')
for i=1:length(dataset)
   dataset(i,11) = (dataset(i,8)^2 + dataset(i,9)^2 + dataset(i,10)^2)/3; % H -> 8, I -> 9, J ->10, K ->11
end
writetable(dataset,'modifiedDataset.xlsx');

Hope this solves your problem.

[Chun Fai Leung]

Thank you so much. Will try after I got home. Sorry for bothering you. But how about I want to write a value of the maximum value of column F and write it on the cell L1. Is it possible? I really appreciate your help so much.

[Amritesh]

You can use max function and store it in dataset(1,12)

dataset(1,12) = max(dataset(:,6));

Hope this solves your problem.

And you can accept the answer if it resolved your issue.

[Chun Fai Leung]

Amazing. You have been extremely helpful.

I modified the code into this

clear

dataset = xlsread()"12.35mW-cm22_gray.xlsx",'5);

for i=1:length(dataset)
   dataset(i,11) = ((dataset(i,1)-dataset(1,1))^2 + (dataset(i,2)-dataset(1,2))^2 + (dataset(i,3)-dataset(1,3))^2)/3
   dataset(1,12) = max(dataset(:,6));
end
writematrix(dataset,'modifiedDataset.xlsx');

Then I would like to plot a graph for the table. So I use the following code.

clear

dataset = xlsread('modifiedDataset.xlsx','Sheet1');

x = dataset(:,6);
y = dataset(:,11);

plot(x,y);

But for column G, the number are actually the particle numbers. I would like to plot several curve in the same graph if possible. In this case, there are 9 particles. Is there any code that could help me identify the particle number and plot the curves respectively. Many thanks again and hopefully, this should be the last question. Thank you so much