topic Re: Counting values in column in Discussions

Counting values in column

EmilyT — Mon, 08 Jan 2024 19:01:00 GMT

Hi,

I need help using the Count function. I think I may be able to us this for what I need but not sure how. I've attached a sample data table. For the Column "day night 5 min intervals" I want to use a formula to count the number of values of the same number in the column "Counter" (This is to help me calculate the length of day and length of night)

I created the day or night columns and the counter columns to get me to my final result in Day/night hours. Unless there is an easier way to do this?

And follow up question: Is there an easy way to turn a duration (number of hours in decimal format) into hr: min. For example - 13.5 hrs becomes 13:30.

Thanks so much.

Re: Counting values in column

dlehman1 — Mon, 08 Jan 2024 19:40:39 GMT

The attached may work - I added a column (intermediate counter) which I then used in a revised Counter column (using the Col Cumulative Sum function). There may be a way to do it using the Date Difference function, but I couldn't get that to work easily. For the second question, what I've done in the past is create two new columns using the Left and Right functions so that 13.5 becomes 13 in the first and .5 in the second. Then create a column using the InMinutes function for the second (e.g. 0.5) column divided by 60. InMinutes measures some number of hours in seconds (why is it called InMinutes?), so the division by 60 makes 0.5 30 seconds. Then you can concatenate the first column (with the hours) and this new column with a : in between. I sure hope someone has a better method!

Re: Counting values in column

jthi — Mon, 08 Jan 2024 20:27:56 GMT

For the first part you should be able to use Col Sum with If (you don't need If if you are fine that all cells have values)

If(Row() == Col Max(Row(), :Counter),
	Col Sum(1, :Counter)
,
	.
);

other option could be to use Col Number(:Counter, :Counter) (add similar if statement if you need it).

For the other question, I would most likely create new formula column with

In Hours(:"Day/night hours"n)

and then change the format (you could also multiply by 60*60 (In hours does this, it converts hours to seconds), JMP datetimes are in seconds so you want to convert your hours to seconds and let JMP handle the formatting from that)

You can also change the format to Best to see the "real" underlying value (seconds).