**You are now following this question**

- You will see updates in your followed content feed.
- You may receive emails, depending on your communication preferences.

# Why do I get NaN values?

3 views (last 30 days)

Show older comments

I have used exactly the same code for Sheet 5 and different column names and I get numerical values but when I use it at Sheet 6 and slightly adjacent columns but the same number of rows I get NaN.

Any idea why this happens?

x1 = readtable('ValidationTest.xls', 'Sheet',6, 'Range','N2:N385 ');

y1 = readtable('ValidationTest.xls', 'Sheet',6, 'Range','A2:A385 ');

x2 = readtable('ValidationTest.xls', 'Sheet',6, 'Range','O2:O868');

y2 = readtable('ValidationTest.xls', 'Sheet',6, 'Range','B2:B868');

P1 = polyfit(x1{:,:}, y1{:,:}, 6 )

P2 = polyfit(x2{:,:}, y2{:,:}, 6)

allX = unique([x1{:,:}; x2{:,:}]);

Pd = polyval(P1, allX) - polyval(P2, allX)

P11 = polyval(P1, allX)

P22 = polyval(P2, allX)

hold on

plot(allX, Pd, 'b')

plot(allX, P11, 'g')

plot(allX, P22, 'r')

##### 13 Comments

madhan ravi
on 22 Apr 2019

I don’t see how a help can be offered without you providing any data file.

Stelios Fanourakis
on 23 Apr 2019

@madhan

All cell values are numerical values with decimals such as 0,043, 0,67 etc.

In the 'Range' there is none empty cell.

Walter Roberson
on 23 Apr 2019

A copy of the data file would make it much easier for us to investigate.

Stelios Fanourakis
on 23 Apr 2019

In that case

%% Setup the Import Options

opts = spreadsheetImportOptions("NumVariables", 15);

% Specify sheet and range

opts.Sheet = "Sheet6";

opts.DataRange = "A2:O873";

% Specify column names and types

opts.VariableNames = ["m2readypng", "A6algpng", "Var3", "Var4", "Var5", "Var6", "Var7", "Var8", "Var9", "Var10", "Var11", "Var12", "Var13", "XX", "XXX"];

opts.SelectedVariableNames = ["m2readypng", "A6algpng", "XX", "XXX"];

opts.VariableTypes = ["double", "double", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "double", "double"];

opts = setvaropts(opts, [3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], "WhitespaceRule", "preserve");

opts = setvaropts(opts, [3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], "EmptyFieldRule", "auto");

% Import the data

ValidationTestS6 = readtable("/Users/steliosphanourakes/Desktop/Matlab/DesMoines/karadokei - Copy/ValidationTest.xls", opts, "UseExcel", false);

%% Convert to output type

e = table2array(ValidationTestS6);

%% Clear temporary variables

clear opts

P1 = polyfit(e(:,4), e(:,2), 6 )

P2 = polyfit(e(:,1), e(:,3), 6)

allX = unique([e(:,3); e(:,4)]);

Pd = polyval(P1, allX) - polyval(P2, allX)

P11 = polyval(P1, allX)

P22 = polyval(P2, allX)

hold on

plot(allX, Pd, 'b')

plot(allX, P11, 'g')

plot(allX, P22, 'r')

I used the automated Matlab method to import from excel.

Until the P1 = polyfit command the data are imported fine as numbers. Can you spot now the difference and why my import method didn't work?? (my method is described at the begining of this thread).

Now using the polyfit command I start getting NaN from that point and onwards. Why is this happening? Does it matter that the two columns I am trying to polyfit are not the same size? Why size should matter?

Stelios Fanourakis
on 24 Apr 2019

Now since I turned all values to cell arrays, data are coming exactly how I want them to.

But by the time they get to polyfit I get the error

Error using polyfit

Too many input arguments.

Error in see (line 33)

P1 = polyfit(e{:,4}, e{:,2}, 6 )

I want to apply a curve fitting between the 4th column of the e sheet and the second column of the same sheet.

Stelios Fanourakis
on 24 Apr 2019

I finally made it run with numbers but I don't think the results are correct since I am not getting plots in the end

{[ NaN]} {[5.6760]} {[ NaN]} {[37.0230]}

{[ NaN]} {[5.6760]} {[ NaN]} {[37.0660]}

{[ NaN]} {[5.6760]} {[ NaN]} {[37.1090]}

{[ NaN]} {[5.6760]} {[ NaN]} {[37.1520]}

{[ NaN]} {[5.6760]} {[ NaN]} {[37.1950]}

{[ NaN]} {[5.6760]} {[ NaN]} {[37.2380]}

{[ NaN]} {[ 0]} {[ NaN]} {[ NaN]}

{[ NaN]} {[ 0]} {[ NaN]} {[ NaN]}

{[ NaN]} {[ 0]} {[ NaN]} {[ NaN]}

{[ NaN]} {[ 0]} {[ NaN]} {[ NaN]}

{[ NaN]} {[ 0]} {[ NaN]} {[ NaN]}

{[ NaN]} {[ 0]} {[ NaN]} {[ NaN]}

Warning: Polynomial is not unique; degree >= number of data points.

> In polyfit (line 74)

In see (line 33)

P1 =

1.0e-03 *

0.3916 0 0 0 0 0 0

Warning: Polynomial is not unique; degree >= number of data points.

> In polyfit (line 74)

In see (line 34)

P2 =

1.0e-03 *

0.3916 0 0 0 0 0 0

Pd =

0

P11 =

4.8020

P22 =

4.8020

Is it correct if I write the commands with the brackets like this?

I want to say the 4th whole column of the cell matrix with the 2nd whole column of the cell matrix as X,Y and apply the 6th degree polynomial. The same underneath for 3rd and st column as X,Y. Is it correct written?

P1 = polyfit(e{4}, e{2}, 6 )

P2 = polyfit(e{3}, e{1}, 6)

allX = unique([e{3}; e{4}]);

Stelios Fanourakis
on 24 Apr 2019

Here are my data. Can you spot the problem Rik?

Stelios Fanourakis
on 24 Apr 2019

In the case of that code

clc;

clear all;

%% Setup the Import Options

opts = spreadsheetImportOptions("NumVariables", 15);

% Specify sheet and range

opts.Sheet = "Sheet6";

opts.DataRange = "A2:O873";

% Specify column names and types

opts.VariableNames = ["m2readypng", "A6algpng", "Var3", "Var4", "Var5", "Var6", "Var7", "Var8", "Var9", "Var10", "Var11", "Var12", "Var13", "XX", "XXX"];

opts.SelectedVariableNames = ["m2readypng", "A6algpng", "XX", "XXX"];

opts.VariableTypes = ["double", "double", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "double", "double"];

opts = setvaropts(opts, [3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], "WhitespaceRule", "preserve");

opts = setvaropts(opts, [3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], "EmptyFieldRule", "auto");

% Import the data

ValidationTestS6 = readtable("/Users/steliosphanourakes/Desktop/Matlab/DesMoines/karadokei - Copy/ValidationTest.xls", opts, "UseExcel", false);

%% Convert to output type

e = table2array(ValidationTestS6);

x1 = e(:,4)

y1 = e(:,2)

x2 = e(:,3)

y2 = e(:,1)

%% Clear temporary variables

clear opts

P1 = polyfit(x1, y1, 6)

P2 = polyfit(x2, y2, 6)

allX = unique([x1; x2]);

Pd = polyval(P1, allX) - polyval(P2, allX)

P11 = polyval(P1, allX)

P22 = polyval(P2, allX)

hold on

plot(allX, Pd, 'b')

plot(allX, P11, 'g')

plot(allX, P22, 'r')

I get numbers until allX. From Pd and onwards I get NaN.

Why is that?

Stelios Fanourakis
on 24 Apr 2019

Finally, after using that code I get results and plots. The point is that not for all excel sheets and datasets I get the exactly same graph, as in the excel. The graph I get in Matlab differs the graph I get from excel for some sheets.

Can you take a look at the script please, and tell me if anything is wrong?

%% Import data from spreadsheet

% Script for importing data from the following spreadsheet:

%

% Workbook: /Users/steliosphanourakes/Desktop/Matlab/DesMoines/karadokei - Copy/ValidationTest.xls

% Worksheet: Sheet12

%

% Auto-generated by MATLAB on 24-Apr-2019 17:55:58

clc;

clear all;

%% Setup the Import Options

opts = spreadsheetImportOptions("NumVariables", 16);

% Specify sheet and range

opts.Sheet = "Sheet12";

opts.DataRange = "A2:P868";

% Specify column names and types

opts.VariableNames = ["m8readypng", "A36algpng", "Var3", "Var4", "Var5", "Var6", "Var7", "Var8", "Var9", "Var10", "Var11", "Var12", "Var13", "XX", "Var15", "XXX"];

opts.SelectedVariableNames = ["m8readypng", "A36algpng", "XX", "XXX"];

opts.VariableTypes = ["double", "double", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "double", "string", "double"];

opts = setvaropts(opts, [3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15], "WhitespaceRule", "preserve");

opts = setvaropts(opts, [3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15], "EmptyFieldRule", "auto");

% Setup rules for import

opts.ImportErrorRule = "omitrow";

opts.MissingRule = "omitrow";

opts = setvaropts(opts, [1, 2, 3, 4], "TreatAsMissing", '');

% Import the data

ValidationTestS12 = readtable("/Users/steliosphanourakes/Desktop/Matlab/DesMoines/karadokei - Copy/ValidationTest.xls", opts, "UseExcel", false);

%% Convert to output type

ValidationTestS12 = table2array(ValidationTestS12);

%% Clear temporary variables

clear opts

e = ValidationTestS12;

x1 = e(:,4)

y1 = e(:,2)

x2 = e(:,3)

y2 = e(:,1)

%after the readtable

mask = isnan(x1) | isnan(y1);

x1(mask,:) = [];

y1(mask,:) = [];

mask = isnan(x2) | isnan(y2);

x2(mask,:) = [];

y2(mask,:) = [];

P1 = polyfit(x1, y1, 6)

P2 = polyfit(x2, y2, 6)

allX = unique([x1; x2]);

Pd = polyval(P1, allX) - polyval(P2, allX)

P11 = polyval(P1, allX)

P22 = polyval(P2, allX)

hold on

plot(allX, Pd, 'b')

plot(allX, P11, 'g')

plot(allX, P22, 'r')

John D'Errico
on 24 Apr 2019

Edited: John D'Errico
on 24 Apr 2019

The file you attached has only one page in it. If I extract those 4 variables, it appears that y2 is constant at 4.8. ABSOLUTELY constant.

If I plot x1 and y1, I see this discreet mess:

That is what this is all about? An absolutely copnstant function, and a that mess?

Sorry, but how do you justify fitting a 6th degree poltynomial to that? You have asked how many questions on this so far?

Stelios Fanourakis
on 24 Apr 2019

Edited: Stelios Fanourakis
on 24 Apr 2019

@John

The graph is correct. It should be like this more or less. By luck, I sent you one dataset with a constant value. Not all sheets are the same. It’s just happened for the particular one. Most of the datasets are curves.

Hopefully, I need to ask as many questions as I need in order to get the right answer. Hope it won’t bother.

Walter Roberson
on 24 Apr 2019

That code is not set up to read the VTest.xlsx file you provided.

The earlier code was set up to read it (once the file name was changed), but it did not take into account that two of the columns were shorter than the others.

### Answers (0)

### See Also

### Categories

### Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!**An Error Occurred**

Unable to complete the action because of changes made to the page. Reload the page to see its updated state.

Select a Web Site

Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .

You can also select a web site from the following list

How to Get Best Site Performance

Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.

Americas

- América Latina (Español)
- Canada (English)
- United States (English)

Europe

- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)

- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)

Asia Pacific

- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)