Skip to main content
P
ptc-54694501-Visitor
1-Visitor
June 9, 2014
Question

Ignore Zero / Blanks from Excel

  • June 9, 2014
  • 3 replies
  • 6030 views

I am using the insert excel component to input data. The data consist of columns with sparatic values in columns C-I. Columns A&B are completely full. I want to graph the sparitic values in C-I against col A. When the data is imported, all of the blank cell come in as zero. when plotting they also plot as zero. I want the plot to connect each value ignoring the zero's that were brought in.

Any ideas?

thanks

Ron

3 replies

M
MikeArmstrong1-Visitor
1-Visitor
June 9, 2014

Could you please provide a typical data set?

M
MikeArmstrong1-Visitor
1-Visitor
June 9, 2014

Surmising that you have similar data, maybe something like this would work.

Clipboard01.jpg

P
ptc-54694501-VisitorAuthor
1-Visitor
June 9, 2014

Data set looks like this about 350 rows

P1c.JPG

W
Werner_E25-Diamond I
25-Diamond I
June 9, 2014

Is there any specific reason for using the Excel component?

I would rather use the READEXCEL function. Empty cells are replaced by NaN (Not-a-Number) by default or anything else you like. If used in a 2D NaN disconnects the graph. If this is not what you want, you can use the filterNaN() function to delete all rows in the matrix which contain at least one NaN.

09.06.png

P.S.: If asking a question here please always state which version of the program you are using. The screenshot you provided looks like you are using Prime, not real Mathcad. The solution given here will work there as well.

If you are sure that 0 is no valid data you can follow Mike's idea and filter out all rows which contain a zero, but you will have to delete the same rows in column A as well to be able to produce a valid plot. You may use augment(A,datacolumn) and combine match() and trim() to do so.

M
MikeArmstrong1-Visitor
1-Visitor
June 9, 2014

If this is not what you want, you can use the filterNaN() function to delete all rows in the matrix which contain at least one NaN.

I don't think that is an option because of his sporadic zero values.

W
Werner_E25-Diamond I
25-Diamond I
June 9, 2014

Mike Armstrong wrote:

If this is not what you want, you can use the filterNaN() function to delete all rows in the matrix which contain at least one NaN.

I don't think that is an option because of his sporadic zero values.

It sure IS an option as those values will not be zero but NaN if he uses READEXCEL instead of the Excel component.

The real problem is that the component replaces emtpty cells by zero and we have no way to change that behaviour. Given the screenshot which was provided your appraoch can be an option as it seems that no valid data will be zero. Column A has to be treated the same so we probably get 7 different columns A for the abscisa.

P
ptc-54694501-VisitorAuthor
1-Visitor
June 9, 2014

Attached is the data I am working with... I have not tried the READEXCEL yet will work on that. If it will plot the points I then want to use the curve fitting functions to produce a curve for the points.

thanks for the quick replys...

Ron

M
MikeArmstrong1-Visitor
1-Visitor
June 9, 2014

Have a look if the attached helps. I have used Werners suggested method. It needs advancing but will be a good start.

W
Werner_E25-Diamond I
25-Diamond I
June 9, 2014

So if you follow my suggestion, I will follow yours and filter the zeros

I still don't like the Excel component but other than in Mathcad it seems to be the only possible way in Prime to embed the data in a convenient way to make the sheet self contained.

The attached should do the job, just do for the other columns what I did for P1.

@Mike: Something seems to be wrong with the data you used - the abscissa values seem to be wrong.

EDIT: No, your data is correct. The discrepancy is because Ron just exported the first 99 rows of his data.

09.06.png

Terms & ConditionsAccessibility statement