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

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

melissacch — Wed, 24 Jan 2018 20:09:55 GMT

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

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

uday_guntupalli — Wed, 24 Jan 2018 20:45:22 GMT

@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

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

melissacch — Wed, 24 Jan 2018 21:01:28 GMT

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.

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

uday_guntupalli — Wed, 24 Jan 2018 23:38:06 GMT

@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")));

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

txnelson — Thu, 25 Jan 2018 03:50:09 GMT

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.

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

melissacch — Thu, 25 Jan 2018 14:11:39 GMT

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