JMP is Not a Spreadsheet

[#1]

It seems to me that a lot of requests appear in these discussions because users attempt to solve a problem or use JMP like a spreadsheet. The rows and columns in a JMP data table resemble a spreadsheet but this appearance is misleading. My intention here is to discuss ways of using JMP, interactively or scripted, that will be rewarding instead of frustrating. I hope that others will join this discussion. Maybe we can reduce the level of frustration in the future that arises from attempts to use JMP in a way that it was not intended.

First of all, a spreadsheet is oriented around a cell. A cell is a location in which to store a value or a formula. It accepts formats. It can be organized with other cells in rows and columns. It is easy to work with rows, columns, or a selection of cells, but they are still individual cells. I can put anything anywhere at any time. That behavior is convenient when working in software that is optimized for consolidating and reporting financial data. JMP, on the other hand, is software for discovery about and between variables using myriad statistical analyses and visualizations. It is oriented around the variable. A data column is a cohesive collection of values for each variable. These related values share the same meaning and, therefore, the same format and other meta-data. A row is also a cohesive collection of values that represent an observation and share row states.

Second, the data table is primarily for storing and organizing data (variables and observations) along with their meta-data. It is not responsible for any kind of analysis. The numerical and graphical analyses happen in the many specialized platforms available through the Analyze and Graph menus. The platforms work with the data table and data filters (change row states). Multiple platforms may be simultaneously opened on the same data set. Multiple platforms maybe combined into a single window when this enhances the analysis.

How else is JMP not a spreadsheet? I will be back with more ideas but it is now your turn.

No software can claim to be all things to all users. I would not expect a word processor to be good at functional data analysis nor would I expect it to be easy to teach it to do so. Many different kinds of software easily and successfully work and play together today so that we may use each of them to their best advantage.

[#2]

I fully agree. I suspect that most new JMP users have basisc or bettter than basic Excel knowledge.

Therefore, in my experience, it would be important to point out the fundamental differences between JMP and Excel as soon as possible.

This would make it possible to get positive results more quickly with JMP.

[#3]

Great input!  Many of us that have been using JMP for years will quickly dismiss anyone proclaiming that they use Excel and it works fine.  This would be the moment to help this temporarily captive audience understand the value of using JMP vs Excel (or any other tool that they use as a hammer).  I'd love to see JMP publish a series of articles highlighting why one would consider moving their work from Excel to JMP.  To JMP users, this is an obvious transition when JMP is required or provides other advantages (efficiency, reproducibility, etc.), to non-JMP users, they don't know what they don't know.

If you're looking for a good example of this, take a look at business-science.io.  They do a great job of documenting a case study of how moving their work from Excel to R gave them a 10-100X productivity boost:    https://www.business-science.io/business/2019/02/20/excel-to-r-part-1.html

[#4]

Great thread @Mark_Bailey . Whenever I introduced JMP to new users I had a simple admonition that I hammered home to those in attendance. It went something like this:

There are three rules for learning JMP:

1. JMP is not Excel.

2. JMP Is Not Excel.

3. JMP IS NOT EXCEL.

Then invariably later on in the session somebody would ask the question, "How do I do 'x' like I can in Excel?" If it wasn't doable or wise in JMP, I'd refer to the three rules to remind everyone that JMP is not Excel.

On a more serious note, I thing a key difference between JMP and spreadsheet applications is the point and click, dynamic interactivity that permeates JMP and is virtually nonexistent in spreadsheet applications. JMP is designed for what I call 'nonlinear discovery'...where the user has the opportunity to move in a sequential fashion through their data and analysis posing certain questions, learning by doing, all in the context of an analysis flow. Compare and contrast this to the what I'll call the 'once and done' of a spread sheet app. Want a bar chart? Make one. Want a pie chart? Make one. etc. But that's as far as you can go. Then it's back to square one to start over. Not so in JMP.

[#5]

Imaging the following. As an experienced user of JMP, you have to switch back to Excel. The main question would be, how can I do this in Excel, what I have done before in JMP.

[#6]

The first thing I teach people when they're first learning JMP is using by columns with stacked data.  I find this to be a good seed to show people the difference between excel and JMP. 

@Mark_Bailey I think it would be great to keep this post (or a summarized post of other people's suggestions) as a sticky on this forum.  This is something I find hard to get through to people who haven't really used JMP before.  

[#7]

---

@vince_faller wrote:

I think it would be great to keep this post (or a summarized post of other people's suggestions) as a sticky on this forum.  This is something I find hard to get through to people who haven't really used JMP before.  

---

Done. We'll keep it up there for awhile.

[#8]

Excel users think in terms of having to build everything they need.  Because of this, they tend to use Formula Columns to generate their stats and Graph Builder to display the results.  Learning that JMP Platforms can make their lives easier is a very eye opening thing for the Excel user.  

Speaking of Formula Columns, another eye opening event, is the fact that JMP Formula's don't have to fit on just one line.  And they can be long and complex if needed.  Several times, new Excel to JMP users have remarked that they never new that more than one statement can be used in a JMP formula.

Another "Wow that's Powerful" item, is the exposure to Excel users on how you do a Vlookup in JMP using Join/Update.  The simplicty of the Join/Update platforms compared to Vlookup makes JMP a real winner.

[#9]

Hi, that's an interesting point. Before I open a new thread, may I bother you with a question? I am trying to achieve the calculations indicated by the blue arrow in the picture attached. This is stacked data for 3 arbitrary specimens with different results over a varying number of days. For each of the different specimens I want to convert the absolute results to relative results where d=0=100% for each specimen. I think my problem is that I don't know the terminology well enough for what I want to do. Would you be able to help?

[#10]

Here are 3 different formulas that will calculate your needed answer.  The first one finds the d0 row for the given specimen based upon Time[d] = 0, while the second finds minimum of Absolute data [a.u.] for the given specimen.

Also, the formula rounds to an integer in each formula.  If you want more precision the Round() function can be removed and the column format adjusted to not allow decimal places.  The third calculated column used the JMP percent standard of the value of 1 being displayed as 100% by using the Percent format and specifying no decimal points.

I have attached my example data table. 

Relative [% of d=0]

curSpecimen = :Specimen;
theRows = Current Data Table() << get rows where( :Specimen == curSpecimen & :Name( "Time [d]" ) == 0 );
d0Row = theRows[1];
d0Time = :Name( "Absolute data [a.u.]" )[d0Row];
Round( (:Name( "Absolute data [a.u.]" ) / d0Time) * 100 );

Relative [% of d=0] with assumption

Round( (:Name( "Absolute data [a.u.]" ) / Col Min( :Name( "Absolute data [a.u.]" ), :Specimen )) * 100 )

Relative [% of d=0] using JMP formating

curSpecimen = :Specimen;
theRows = Current Data Table() << get rows where( :Specimen == curSpecimen & :Name( "Time [d]" ) == 0 );
d0Row = theRows[1];
d0Time = :Name( "Absolute data [a.u.]" )[d0Row];
:Name( "Absolute data [a.u.]" ) / d0Time;