cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Did you get an answer that solved your problem? Please mark it as an Accepted Solution so others with the same problem can find the answer easily. X

Enter "NaN" via excel component

SN_8612563
3-Visitor

Enter "NaN" via excel component

Hi!

I have written som output in the excel component, and I would like to define a few values as "NaN" (they are out side my range of interest), but the excel component does not appear to accept NaN from excel. Are there any workarounds?

 

Thank you! (using MC P7)

 

ExcelNaN.PNG

1 ACCEPTED SOLUTION

Accepted Solutions

The reason for the error is because Excel does not support a variable for "not a value" and treats the NaN in your spreadsheet as normal Text.

When you read that in in Prime you try to multiply that text by a unit (seconds) which causes Prime to throw the error.

A solution can consist in not assigning the unit in the Excel component but rather replace the "NaN" by NaN after reading in the columns and only then add the units.

Here is a small function which replaces any string for a NaN. You call it vectorized and add the unit:

 

EDIT: I am not allowed to embed a picture in this posting because the system says that I reached my image upload limit. Thats silly! Images embedded in a posting should not be automatically added to my "gallery" but rather be connected to the specific reply like attachments are.

So I have to attach the pic - hope this will work.

 

The routine as shown replaces EVERY cell with a string/text in it by a NaN.

If you'd rather just look for a specific string like "NaN" you would use Str2NaN(v):=if(v="NaN",NaN,v) instead.

 

View solution in original post

2 REPLIES 2

The reason for the error is because Excel does not support a variable for "not a value" and treats the NaN in your spreadsheet as normal Text.

When you read that in in Prime you try to multiply that text by a unit (seconds) which causes Prime to throw the error.

A solution can consist in not assigning the unit in the Excel component but rather replace the "NaN" by NaN after reading in the columns and only then add the units.

Here is a small function which replaces any string for a NaN. You call it vectorized and add the unit:

 

EDIT: I am not allowed to embed a picture in this posting because the system says that I reached my image upload limit. Thats silly! Images embedded in a posting should not be automatically added to my "gallery" but rather be connected to the specific reply like attachments are.

So I have to attach the pic - hope this will work.

 

The routine as shown replaces EVERY cell with a string/text in it by a NaN.

If you'd rather just look for a specific string like "NaN" you would use Str2NaN(v):=if(v="NaN",NaN,v) instead.

 

Thank you very much @Werner_E 😀

Top Tags