cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
%3CLINGO-SUB%20id%3D%22lingo-sub-803057%22%20slang%3D%22en-US%22%20mode%3D%22CREATE%22%3EAvg%20%26amp%3B%20SD%20caluclation%20for%20multiple%20columns%20in%20JMP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-803057%22%20slang%3D%22en-US%22%20mode%3D%22CREATE%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20quite%20new%20to%20JMP%20and%20looking%20for%20some%20advise%20how%20to%20performance%20below%20tasks%3A%3CBR%20%2F%3E1.%20can%20JMP%20combine%20multiple%20files%20with%20multiple%20random%20tabs%20together%20into%20one%20data%20table%3F%3C%2FP%3E%3CP%3E2.%20in%20the%20data%20table%2C%20how%20to%20calculate%20multiple%20columns%20(with%20the%20same%20column%20name%20and%20column%20number%20is%20random)%20average%20and%20standard%20deviation%20as%20two%20new%20columns%3F%3C%2FP%3E%3CP%3E3.%20can%20I%20make%20a%20plot%20using%20%2B-%203%20sigma%20as%20spec%20range%2C%20and%20other%20raw%20columns%20data%20as%20scatter%20plot%20to%20quantify%20if%20all%20within%20spec%20or%20not%3F%26nbsp%3B%3C%2FP%3E%3CP%3E4.%20Can%20JMP%20report%20pass%20or%20fail%20if%20any%20data%20point%20out%20of%20the%20spec%3F%20even%20for%20linear%20regression%20using%20Fit%20Y%20by%20X%20model%3F%20(I%20tried%20to%20use%20Fit%20Y%20by%20X%20but%20unable%20to%20draw%20any%20spec%20properly%2C%20unable%20to%20find%20that%20feature%20available)%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI've%20attached%204%20excel%20files%20and%20one%20png%20to%20show%20what%20I'm%20trying%20to%20do%20for%20your%20reference.%20Thank%20you%20for%20your%20help%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-803057%22%20slang%3D%22en-US%22%20mode%3D%22CREATE%22%3E%3CLINGO-LABEL%3EAdvanced%20Statistical%20Modeling%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAutomation%20and%20Scripting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EBasic%20Data%20Analysis%20and%20Modeling%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-803127%22%20slang%3D%22en-US%22%20mode%3D%22CREATE%22%3ERe%3A%20Avg%20%26amp%3B%20SD%20caluclation%20for%20multiple%20columns%20in%20JMP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-803127%22%20slang%3D%22en-US%22%20mode%3D%22CREATE%22%3E%3CP%3EOthers%20should%20chime%20in%20since%20my%20answers%20may%20have%20ideas%20that%20I%20did%20not%20think%20of.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1)%20I%20am%20assuming%20you%20are%20trying%20to%20import%20Excel%20workbooks%20since%20you%20mentioned%20several%20tabs.%20You%20can%20certainly%20import%20an%20Excel%20workbook%20with%20multiple%20tabs%20into%20a%20single%20data%20table.%20You%20can%20import%20multiple%20Excel%20files%20at%20once%2C%20but%20they%20may%20not%20be%20into%20a%20single%20table.%20I%20do%20not%20think%20you%20can%20do%20both%20at%20the%20same%20time.%20But%20you%20could%20do%20it%20as%20a%20two-step%20process%3A%20bring%20in%20each%20file%20with%20the%20multiple%20tabs%20and%20then%20join%20the%20resulting%20JMP%20data%20tables.%3C%2FP%3E%0A%3CP%3E2)%20You%20should%20read%20the%20post%20in%20the%20discussions%20that%20state%20that%20JMP%20is%20not%20a%20spreadsheet.%20You%20can%20calculate%20the%20average%20and%20standard%20deviation%20for%20multiple%20columns%20using%20the%20Table%20%26gt%3B%20Summary%20command.%20This%20will%20put%20the%20results%20into%20a%20new%20data%20table.%20If%20you%20MUST%20have%20the%20results%20in%20the%20same%20data%20table%20as%20the%20raw%20data%2C%20then%20you%20can%20use%20column%20formulas%20to%20calculate%20averages%20and%20standard%20deviations.%20There%20are%20other%20ways%20that%20can%20accomplish%20this%20task%20depending%20on%20exactly%20what%20you%20would%20like%20to%20do%20and%20why.%20Sometimes%20the%20best%20answer%20may%20not%20always%20be%20what%20you%20are%20thinking%20of%20doing.%3C%2FP%3E%0A%3CP%3E3)%20If%20you%20fit%20a%20linear%20model%2C%20what%20%22sigma%22%20are%20you%20using%20for%20the%20bands%3F%20I%20would%20worry%20about%20misleading%20graphs.%20Using%20the%20standard%20deviation%20of%20the%20response%20would%20not%20be%20appropriate.%20Some%20of%20that%20variance%20has%20been%20explained%20by%20the%20X-variable.%20You%20could%20use%20the%20RMSE%20of%20the%20model%20fit%2C%20but%20is%20that%20really%20the%20proper%20error%3F%20Are%20you%20wanting%20to%20form%20a%2099.7%25%20confidence%20interval%20for%20the%20fit%3F%2099.7%25%20confidence%20interval%20for%20the%20prediction%3F%20Those%20are%20options%20that%20are%20available%20under%20the%20red%20triangle%20for%20the%20linear%20model%20that%20was%20fit%20to%20the%20data.%20Ultimately%2C%20it%20really%20depends%20on%20what%20you%20are%20trying%20to%20do%20with%20that%20interval.%3C%2FP%3E%0A%3CP%3E4)%20Yes%2C%20this%20can%20be%20done.%20You%20will%20likely%20want%20to%20use%20a%20formula%20column%20to%20determine%20if%20a%20value%20is%20in%20spec%20or%20not.%20Having%20that%20information%20in%20a%20column%20will%20allow%20you%20to%20easily%20mark%20the%20data%20points%20as%20in%2Fout%20of%20spec%20in%20Graph%20Builder.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20hope%20that%20others%20will%20chime%20in%20with%20more%20details.%20It%20may%20be%20better%20for%20you%20to%20have%20each%20question%20separated%20into%20a%20separate%20thread%20as%20they%20are%20quite%20different.%20More%20specific%20can%20then%20be%20asked%20and%20answered%20for%20each%20question.%20Best%20of%20luck%20on%20your%20JMP%20learning%20path!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Choose Language Hide Translation Bar
qli107966
Level I

Avg & SD caluclation for multiple columns in JMP

Hello,

 

I'm quite new to JMP and looking for some advise how to performance below tasks:
1. can JMP combine multiple files with multiple random tabs together into one data table?

2. in the data table, how to calculate multiple columns (with the same column name and column number is random) average and standard deviation as two new columns?

3. can I make a plot using +- 3 sigma as spec range, and other raw columns data as scatter plot to quantify if all within spec or not? 

4. Can JMP report pass or fail if any data point out of the spec? even for linear regression using Fit Y by X model? (I tried to use Fit Y by X but unable to draw any spec properly, unable to find that feature available)


I've attached 4 excel files and one png to show what I'm trying to do for your reference. Thank you for your help in advance.

1 REPLY 1

Re: Avg & SD caluclation for multiple columns in JMP

Others should chime in since my answers may have ideas that I did not think of.

 

1) I am assuming you are trying to import Excel workbooks since you mentioned several tabs. You can certainly import an Excel workbook with multiple tabs into a single data table. You can import multiple Excel files at once, but they may not be into a single table. I do not think you can do both at the same time. But you could do it as a two-step process: bring in each file with the multiple tabs and then join the resulting JMP data tables.

2) You should read the post in the discussions that state that JMP is not a spreadsheet. You can calculate the average and standard deviation for multiple columns using the Table > Summary command. This will put the results into a new data table. If you MUST have the results in the same data table as the raw data, then you can use column formulas to calculate averages and standard deviations. There are other ways that can accomplish this task depending on exactly what you would like to do and why. Sometimes the best answer may not always be what you are thinking of doing.

3) If you fit a linear model, what "sigma" are you using for the bands? I would worry about misleading graphs. Using the standard deviation of the response would not be appropriate. Some of that variance has been explained by the X-variable. You could use the RMSE of the model fit, but is that really the proper error? Are you wanting to form a 99.7% confidence interval for the fit? 99.7% confidence interval for the prediction? Those are options that are available under the red triangle for the linear model that was fit to the data. Ultimately, it really depends on what you are trying to do with that interval.

4) Yes, this can be done. You will likely want to use a formula column to determine if a value is in spec or not. Having that information in a column will allow you to easily mark the data points as in/out of spec in Graph Builder. 

 

I hope that others will chime in with more details. It may be better for you to have each question separated into a separate thread as they are quite different. More specific can then be asked and answered for each question. Best of luck on your JMP learning path!

Dan Obermiller