Choose Language Hide Translation Bar
Highlighted
sweetycha
Level III

Calculate Average by two groups

Hi All

How to calculate average of total visit by location and by day of the week.

like average of all Sundays  total visits by facility and  all Mondays total visits by facility (attached sample)

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Thierry_S
Level VI

Re: Calculate Average by two groups

Hi,
The Column Mean function lets you add as many "By" parameters as you need.
In the Formula edit window, choose Statistical > Col Mean, select the column containing the data, press "," to add the "By" parameter, amd repeat the last step as needed.
I hope that this is what you were looking for.

Best regards,
TS
Thierry R. Sornasse

View solution in original post

9 REPLIES 9
Highlighted
Thierry_S
Level VI

Re: Calculate Average by two groups

Hi,
The Column Mean function lets you add as many "By" parameters as you need.
In the Formula edit window, choose Statistical > Col Mean, select the column containing the data, press "," to add the "By" parameter, amd repeat the last step as needed.
I hope that this is what you were looking for.

Best regards,
TS
Thierry R. Sornasse

View solution in original post

Highlighted
sweetycha
Level III

Re: Calculate Average by two groups

Thank you..

Highlighted
sweetycha
Level III

Re: Calculate Average by two groups

Hi TS

I imported the data in duration format from excel and in JMP it is showing as time format. I tried to convert in col info in the duration. It is not working as planned.

Please advise. After that I need to calulate an average by two groups 

time jmp.PNG

Highlighted
Thierry_S
Level VI

Re: Calculate Average by two groups

Hi,
I'm not sure that it will help but have your tried to change the Format of your duration column to "Best" which will represent the data in decimal format. Ideally, it would be easiest for me to test a couple of approaches if you could share an example of the data (nothing confidential or proprietary).
Thierry R. Sornasse
Highlighted
sweetycha
Level III

Re: Calculate Average by two groups

Thank you

Attached is the sample file  where G H I are  duration  ( hours and min) and I would like to calculate mean by site and day

Highlighted
Thierry_S
Level VI

Re: Calculate Average by two groups

Hi,

I looked at your data and the problem is that JMP does not understand correctly durations in "h:m" format where the number of hours are greater than 24 (i.e. you have durations of 102:36 for example). I tried a couple of approaches but this is beyond my skill level.

 

I would recommend posting a new Discussion topic with your specific problem: How to import duration data expressed as "h:m" where the number of hours exceeds 24.

 

Sorry I could not be of much help.

 

Sincerely,

 

TS

Thierry R. Sornasse
Highlighted
sweetycha
Level III

Re: Calculate Average by two groups

Sure. I will post in new discussion. Thank you for looking at my data. I very much appreciate you

Highlighted

Re: Calculate Average by two groups

Hi @sweetycha,

Which version of JMP are you using? Also agree that a different post for the new problem would be best.

By the way, I was able to import using the Excel Import Wizard and all imported correctly for JMP 15.1.0
Chris Kirchberg
Principal Systems Engineer, Life Sciences - JMP Global Technical Enablement
SAS Institute, Inc. - Denver, CO
Tel: +1-919-531-9927 ▪ Mobile: +1-303-378-7419 ▪ E-mail: chris.kirchberg@jmp.com
JMP – A Division of SAS Institute | www.jmp.com
Highlighted

Re: Calculate Average by two groups

I take that back, only one of the columns came in as hr:m. The other two did not. The came in as hr:m:s which did not give the correct format. Upon closer inspection within Excel, the format of the cells are in a date-time format, not a duration format. Some are in a time format. I am not sure if Excel even understands Duration format. That is probably why JMP is doing something unexpected.

I saved the Excel file as a CSV and then imported using the Preview Option. Then I could set the columns to numeric and duration of hr:m.

All imported as expected then.

Chris Kirchberg
Principal Systems Engineer, Life Sciences - JMP Global Technical Enablement
SAS Institute, Inc. - Denver, CO
Tel: +1-919-531-9927 ▪ Mobile: +1-303-378-7419 ▪ E-mail: chris.kirchberg@jmp.com
JMP – A Division of SAS Institute | www.jmp.com
Article Labels