The other day, the JMP Japan Division gave an explanation about ``Excel Profile'' at a seminar called ``JMP Information Bureau.'' This was a feature that we hadn't really introduced before, but many people participated and asked us a few questions.
Excel Profile is a feature that allows you to visualize formulas created in Microsoft Excel using JMP's "Profile". This add-in is installed when you select the Excel add-in when you install JMP.
The JMP documentation provides an example of using the Excel add-in, based on a formula that calculates overall cost from inventory (Amount Stocked) and demand (Demand) using sample data ( Demand.xlsx ). An example of launching a JMP profile to visualize a formula is provided.
Corresponding section of JMP Help (Japanese)
Excel model example (jmp.com)
*Excel add-in is only available for the Windows version of JMP. It is not available in the Macintosh version of JMP.
This feature is useful for those who are used to creating formulas in Excel as a way to visualize formulas. However, similar things can be done using JMP's data tables and calculation formulas, expanding the range of applications.
Therefore, in this blog, we will introduce how to implement an example Excel profile using only JMP.
Steps to visualize calculation formulas created in JMP using profiles
To perform the equivalent of the Excel profile example above only in JMP, follow the steps (1, 2, 3) below.
(*The above series of steps can be confirmed in the data table “ Demand_ex1.jmp ” attached to this blog.)
Step 1. Set minimum and maximum values for each variable
Create a data table like this:
The first line sets the minimum value of each variable (Amount Stocked to Expiration Cost).
The second line sets the maximum value of each variable (Amount Stocked to Expiration Cost).
The third line sets the value corresponding to Input of Demand.xlsx, but this line is only set for convenience and does not need to be present.
Step 2. Setting the calculation formula
For the last column "Overall Cost", create a formula corresponding to cell B8 (Output) of Demand.xlsx.
Step 3. Run the profile
To visualize the formula created in step 2 in the profile, select [Graph] > [Profile] and specify the column "Overall Cost" with the formula set to [Y, Prediction formula]. Then, a profile similar to the one created with Excel profile will be output.
A feature called "Expansion of intermediate expressions" in the profile
If there are cell dependencies in Excel (for example, a cell in A1 is calculated using A2 and A3, and a cell in A5 is calculated using A1 and A4), this cannot be handled with an Excel profile. . However, it is possible to create a data table in JMP and run the profile as shown above.
As an example, I present the following data table, which is an improvement on the previous example. The difference from before is that there is a column "dif(SD)" that calculates Amount Stocked - Demand, and the calculation formula for "Overall Cost", which is a column used in the profile, is created using this "dif(SD)". I am.
(*Please check " Demand_ex2.jmp " attached to this blog.)
Using this data table and outputting the profile using similar steps, the output will look like this:
In this example, the three columns "dif(SD)", "Air Freight", and "Experimentation Cost" are used as the calculation formula for "Overall Cost", so the horizontal axis displays the profile for these three columns. .
However, since "dif(SD)" is calculated from "AmoutStocked" and "Demand", you may want to use these two as the axis of the profile. In that case, use the " Expand intermediate formula " option in the profile.
This option is located at the bottom left of the "Profile" settings, and when checked and executed, the columns used in the "dif(SD)" calculation formula are expanded, and "Amount Stocked" and "Demand" are the axis of the profile. will be displayed as .
When saving a prediction formula from a JMP report such as "Logistic Fit", the formula may be saved across several columns. At times like this, it is useful to remember the ``expansion of intermediate calculation formulas'' described in this article.
*This blog uses the latest version "JMP 18" at the time of blog creation.
by Naohiro Masukawa (JMP Japan)
Naohiro Masukawa - JMP User Community
