cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
melissacch
Level II

Formula Data Type Issue? - Wrong output when comparing two columns

Hi,

 

I'm getting a strange issue in JMP that I can't seem to explain. In my table I am comparing values in two columns. Column "A" is just a simple continuous, numeric column with integer values ranging from 0-100. Column "B" is another simple continous, numeric columns with numbers ranging from 0 - 1, all with precision up to two decimal places. I then create a column "C" to directly compare "A" and "B" by setting a formula in "C": B*100. However, when I then compare columns "A" and "C", in a few cases, A and C are indeed equal, but JMP says they are not. This should be a simple comparison, but for some reason certain numbers aren't comparable. Changing column "C" to character and then back to numeric fixes this issue, but I feel like I shouldn't really have to do this...Can anyone please explain the underlying issue?

 

I've attached an example dataset that lists all the possible numbers. You can see that the issue comes up in rows 7, 14, 28, 29, 55, 56, 57, and 58.

 

Would appreciate any insight here.

Thank you,

Melissa

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Formula Data Type Issue? - Wrong output when comparing two columns

The issue is with floating point/binary numbers.  Not all numbers can be exactly saved as exactly input.  That is one of the long term facts with computers.  And JMP stores it's numerical data in this formula, and then upon display, corrects it back to the corrected value.  This is an issue with almost all systems that perform numerical analysis.

If I remember back.....way back, I believe that the number 3 can not be stored, and in reality is stored as something like 2.9999999999998.  I could be wrong about the number 3, but I hope you get the idea.

Jim

View solution in original post

5 REPLIES 5
uday_guntupalli
Level VIII

Re: Formula Data Type Issue? - Wrong output when comparing two columns

@melissacch , 
 It is a rounding error issue. If you see the data table I attached, I included a new column where the difference between both the columns are calculated. There is a miniscule value this is why JMP  returns unequal. 

 

Hope this helps 

Best
Uday
melissacch
Level II

Re: Formula Data Type Issue? - Wrong output when comparing two columns

Hi @uday_guntupalli


Thanks so much for the response. I should have looked at the difference and seen it was a rounding issue. Any idea why JMP is generating these miniscule differences though? I manually input these numbers, so there shouldn't be any further decimals/precision.

uday_guntupalli
Level VIII

Re: Formula Data Type Issue? - Wrong output when comparing two columns

@melissacch , 
       Instead of copying the data or manually entering it - could you try to script your table ? 
Here is an example. I cant figure out an easy way to generate a series of decimals. But if you were to do something like this, you would avoid the issue of rounding I would think. 

dt = New Table(); 
Set1 = Index(1,100);
Set2 = Index(0.01,1); // This doesn't work 
dt << New Column("A",Format("Fixed Decimal",10,2),Numeric,Continuous,<< Set Values(Set1))
   << New Column("B",Format("Fixed Decimal",10,2),Numeric,Continuous,<< Set Values(Set2))// Will Fail 
   << New Column("B*100",Format("Fixed Decimal",10,2),Numeric,Continuous,Formula(:B*100))
   << New Column("Check1",Format("Fixed Decimal",10,2),Numeric,Continuous,Formula(:A == :Name("B*100")));
Best
Uday
txnelson
Super User

Re: Formula Data Type Issue? - Wrong output when comparing two columns

The issue is with floating point/binary numbers.  Not all numbers can be exactly saved as exactly input.  That is one of the long term facts with computers.  And JMP stores it's numerical data in this formula, and then upon display, corrects it back to the corrected value.  This is an issue with almost all systems that perform numerical analysis.

If I remember back.....way back, I believe that the number 3 can not be stored, and in reality is stored as something like 2.9999999999998.  I could be wrong about the number 3, but I hope you get the idea.

Jim
melissacch
Level II

Re: Formula Data Type Issue? - Wrong output when comparing two columns

Ah, yes, you're right. Thanks so much!