Calculate Average by two groups

[#1]

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

[#2 · Accepted solution]

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

[#3]

Thank you..

[#4]

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 

[#5]

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

[#6]

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

[#7]

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

[#8]

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

[#9]

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

[#10]

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.