topic Re: Calculate Average by two groups in Discussions

Calculate Average by two groups

sweetycha — Fri, 09 Jun 2023 14:42:54 GMT

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

Re: Calculate Average by two groups

Thierry_S — Tue, 23 Jun 2020 22:56:34 GMT

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

Re: Calculate Average by two groups

sweetycha — Wed, 24 Jun 2020 00:09:14 GMT

Thank you..

Re: Calculate Average by two groups

sweetycha — Wed, 24 Jun 2020 17:27:00 GMT

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

Re: Calculate Average by two groups

Thierry_S — Wed, 24 Jun 2020 17:36:14 GMT

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).

Re: Calculate Average by two groups

sweetycha — Wed, 24 Jun 2020 18:12:15 GMT

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

Re: Calculate Average by two groups

Thierry_S — Wed, 24 Jun 2020 18:41:42 GMT

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,

Re: Calculate Average by two groups

sweetycha — Wed, 24 Jun 2020 19:06:38 GMT

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

Re: Calculate Average by two groups

Chris_Kirchberg — Wed, 24 Jun 2020 20:23:42 GMT

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

Re: Calculate Average by two groups

Chris_Kirchberg — Wed, 24 Jun 2020 20:39:33 GMT

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.