i have written a while loop which displays 'yes' or 'no' if the difference is less than 0.5 for a given problem. how can i write this output into an excel sheet.

this is the code i have used. it gives the desired result in the command window but i need it to write the output into an excel sheet. how can i incorporate that?

data=readtable("DATA.xlsx");
X=table2array(data);
i=1;
j=2;
while i<57
    M=max(X(:,i));
    n = mean(X(M-50:end,j),'omitnan');
    m= M-50;    
    while m<M+1
        y=X(m,j);
        z=((y-n)/n)*100;
        disp(z)
        if (0.5>z) && (-0.5<z)
            disp("YES")
        else
            disp("NO")
        end
        m = m+1;
    end
    i=i+2;
    j=j+2;
end

[KSSV]

You got a matrix X, what exact operations you are doing? What you are expecting? You made the ocde complex, this can be done in few steps.

[Aditi]

yes but the output matrix is only showing the last value.

it is my first time using matlab so if you could help with making a simpler code for this problem.

[Siddharth Bhutiya]

Can you attach the DATA.xlsx? As KSSV mentioned there might be ways to simplify the code a bit.

Answer 1

[Arun]

Hey Aditi,

One possible way to do so is:

data=readtable("DATA.xlsx");
X=table2array(data);
i=1;
j=2;
while i<57
    M=max(X(:,i));
    n = mean(X(M-50:end,j),'omitnan');
    m= M-50;  
    counterToPrint=1; %increment it for each row
    % Specify the file name and sheet name
    filename = 'example.xlsx';
    sheet = 'Sheet1';
    while m<M+1
        y=X(m,j)
        z=((y-n)/n)*100;
        disp(z)
        %location to write 
        location = ['A' num2str(counterToPrint)]; % Use the column accordingly
        if (0.5>z) && (-0.5<z)
            xlswrite(filename,{"Yes"}, sheet, location); %write to the sheet
        else
           xlswrite(filename,{"No"}, sheet, location);   %write to the sheet
        end
        counterToPrint = counterToPrint + 1;
        m = m+1;
    end
    i=i+2;
    j=j+2;
end

Hope this helps.

[Aditi]

thank you but this is not making any excel sheet.

[Aditi]

if i use this then only the last value is writing in the excel sheet. how do i make all of them write into the sheet?

data=readtable("DATA.xlsx");
X=table2array(data);
i=1;
j=2;
while i<57
    M=max(X(:,i));
    n = mean(X(M-50:end,j),'omitnan');
    m= M-50;  
    counterToPrint=1;
    while m<M+1
        y=X(m,j);
        z=((y-n)/n)*100;
        disp(z)
        if (0.5>z) && (-0.5<z)
            t="yes";
            disp(t)
        else
            t="no";
            disp(t)
        end
        counterToPrint=counterToPrint+1;      
        m = m+1;
        list1='D1';    
        wholecontent1=(t);
        outputfilename1=("checking3.xlsx");
        writematrix(wholecontent1,outputfilename1,'Range',list1)
        list2='C1';
        wholecontent2=(z);
        outputfilename2=("checking3.xlsx");
        writematrix(wholecontent2,outputfilename2,'Range',list2)
    end
    i=i+2;
    j=j+2;
end

[Arun]

try these modifications to your code.

data=readtable("DATA.xlsx");
X=table2array(data);
i=1;
j=2;
counterToPrint=1;
while i<57
    M=max(X(:,i));
    n = mean(X(M-50:end,j),'omitnan');
    m= M-50;  
    while m<M+1
        y=X(m,j);
        z=((y-n)/n)*100;
        disp(z)
        if (0.5>z) && (-0.5<z)
            t="yes";
            disp(t)
        else
            t="no";
            disp(t)
        end
        counterToPrint=counterToPrint+1;      
        m = m+1;
        sheet = 'Sheet1';
        list1= ['D' num2str(counterToPrint)];    
        wholecontent1=(t);
        outputfilename1=("checking3.xlsx");
        xlswrite(outputfilename1,{wholecontent1}, sheet, list1);
        list2= ['C' num2str(counterToPrint)];
        wholecontent2=(z);
        outputfilename2=("checking3.xlsx");
        xlswrite(outputfilename2,{wholecontent2}, sheet, list2);
    end
    i=i+2;
    j=j+2;
end

[Aditi]

xlswrite is not supported in this version i think.

how can i use writematrix instead?

[Arun]

Hey @Aditi, Use this code with writematrix function call, this should help you:

data=readtable("DATA.xlsx");
X=table2array(data);
i=1;
j=2;
counterToPrint=1;
while i<57
    M=max(X(:,i));
    n = mean(X(M-50:end,j),'omitnan');
    m= M-50;  
    while m<M+1
        y=X(m,j);
        z=((y-n)/n)*100;
        disp(z)
        if (0.5>z) && (-0.5<z)
            t="yes";
            disp(t)
        else
            t="no";
            disp(t)
        end      
        m = m+1;
        sheet = 'Sheet1';
        list1= ['D' num2str(counterToPrint)];    
        wholecontent1=(t);
        outputfilename1=("checking3.xlsx");
        writematrix(wholecontent1,outputfilename1, 'Sheet',sheet,'Range', list1);
        %xlswrite(outputfilename1,{wholecontent1}, sheet, list1);
        list2= ['C' num2str(counterToPrint)];
        wholecontent2=(z);
        outputfilename2=("checking3.xlsx");
        writematrix(wholecontent2,outputfilename2, 'Sheet', sheet, 'Range', list2);
        %xlswrite(outputfilename2,{wholecontent2}, sheet, list2);
        counterToPrint=counterToPrint+1;
    end
    i=i+2;
    j=j+2;
end

[Aditi]

hey this works but the excel sheet only has the last value. how can i add values of every 2nd column and the value of 't' in the excel sheet?

[Aditi]

it is working now. thank you so much.

[Aditi]

and what can i do if i have to read an excel sheet and display all the values which gives 'no' as a result?

[Arun]

data = readtable('checking3.xlsx');

display = data(find(data.Var2 =="no"),:)

[Aditi]

thanks a lot

[Aditi]

is there any way to put the above code into a loop for writing in the excel sheet as in if i want values in 2nd column, 3rd and so on?

[Walter Roberson]

Writing to an excel sheet in a loop is inefficient. xlsx files are zipped directories of XML files, so unless you are using the UseExcel option, every time you write to the file it has to unzip the file and parse the XML and write new XML and zip back up.

It is much more efficient to read the file into memory, change memory, then write the final result.

[Aditi]

oh okay thanks

[Aditi]

data = readtable('checking3.xlsx');
display = data(find(data.Var2 =="no"),:)

while using this it is giving zll the values. what if i want it to display only the values which give the result 'no'?

[Aditi]

all*

[Walter Roberson]

As a matter of efficiency, but not a change in functionality:

data = readtable('checking3.xlsx');
display = data(data.Var2=="no",:)

This will give the same result as your find() version, just a little faster.

It should give you just the rows that match "no" in the second column.... assuming the xlsx does not have any header.

[Aditi]

yes it works. thank you.

Answer 2

[Walter Roberson]

data=readtable("DATA.xlsx");
X=table2array(data);
i=1;
j=2;

decisions = strings(0);
decision_count = 0;

while i<57
    M=max(X(:,i));
    n = mean(X(M-50:end,j),'omitnan');
    m= M-50;    
    while m<M+1
        y=X(m,j);
        z=((y-n)/n)*100;
        disp(z)
        decision_count = decision_count + 1;
        if (0.5>z) && (-0.5<z)
            decisions(decision_count) = "YES";
            disp("YES")
        else
            decisions(decisions_count) = "NO";
            disp("NO")
        end
        m = m+1;
    end
    i=i+2;
    j=j+2;
end

writematrix(decisions, FILENAME)