Write on unknown cell on Excel

I have a database on Excel and, using matlab, I want to find the values out of a predefined range. If the values are out of that range I want to replace them with the mean value of them. I've been able to identify the values out of range but I can't replace them with the mean value because I do not exactly know where are the values out of range. I leave a piece of code here:

filename = 'table.xlsx';
sheet = 3;
[table tit]=xlsread(filename,sheet);

phys_carac = table(:,2);
values_hr = table(:,4);

%Set range
  hr_max = 140;
  hr_min = 60;

hr_table = [];

%calculate mean values. There are more physical characteristics, that's why I calculate first all the means.

for i = 1:length(phys_carac)
    if phys_carac(i) == 1 
        hr = values_hr(i);
        hr_table=[hr_table;hr];
        mean_hr = round(mean(hr_table));
end

%Find values out of range and replace with mean value

for i = 1:length(phys_carac)
      if(values_hr(i) < hr_min) && (values_hr(i) < hr_max)
           %here I want to write the mean value
           xlwrite(newtable,mean_hr,1,'unknown cell');
      end
end

Is there any way to do this? May I choose another way?

Note: I'm using Mac, that's why I use xlwrite (not default matlab function), works as xlswrite.

Answer 1

[Image Analyst]

In general, get a logical map of where your values are out of range

outOfRange = (values_hr < hr_min) || (values_hr > hr_max);

outOfRange is a 2-D map (the same size as values_hr) of true or false values saying if values_hr is out of range or not at each element.

Then get the mean of everything in a 3-by-3 window, or whatever size and shape you want.

localMeans = conv2(values_hr, ones(3)/9, 'same');

3 is the window width. You can make it bigger if you want.

Now replace the out of range elements with the mean elements.

values_hr(outOfRange) = localMeans(outOfRange);

Sorry, I didn't scrutinize your code, so this is just a general purpose way of replacing outliers with local means. No for loop(s) needed. Let me know if you need more help.

[Mikel Mateo]

First of all, thank you for the quick answer, I didn't expect it to be that quick! As for your answer, I get the idea and it runs for only one variable. As I got many on the same row as the values_hr it makes it more complex (maybe I should have specified the structure of my Excel).

Nevertheless, it helped me to figure out the problem on another perspective, your perspective, and I managed to solve the problem! Thank you very much!

[Image Analyst]

If you have lots of "outliers" in a single row, there are outlier detection methods that you can use like median absolute deviation, RANSAC, and others http://www.mathworks.com/matlabcentral/fileexchange/3961