stats % before a certain time

[cpark4]

I'm trying to find the percentage of the times in the below list that occurs before 2PM. Anyone know a good formula for that? Please let me know if I'm not communicating this clearly.

So, what percentage of the times below is before 2PM?

9:24:41 AM
5:15:45 PM
12:32:52 PM
10:10:31 PM
3:11:23 PM
9:02:26 AM
9:47:45 AM
4:56:17 PM
12:53:58 PM
1:55:35 PM
10:38:20 AM
9:05:54 AM
10:13:55 AM
11:38:01 AM
2:15:40 PM
3:05:54 PM
9:22:59 AM
2:55:53 PM
9:17:51 AM
3:07:22 PM
6:08:28 PM
7:35:52 AM
9:17:43 AM
7:55:55 PM
3:01:34 PM
4:39:32 PM
1:58:32 PM
3:20:10 PM
11:42:19 AM
5:04:27 PM
8:50:13 AM
11:21:39 AM
11:43:24 AM
10:46:06 AM
9:56:27 AM
10:16:35 AM
4:44:01 PM
12:15:23 PM
2:59:21 PM
5:40:20 PM
10:02:28 AM
8:23:34 AM
6:11:53 PM
12:51:34 PM
10:16:57 AM

 

[Dutchy]

something like

=COUNTIF(A1:A45,"<14:00")/45

formatted as percentage

 

[cpark4]

Cool, I think that's it. I was almost there when I saw this post.

Just so that I understand, what does the /45 mean?
I understand the <14:00...

Also, is there a way to make sure the calcuation is right?

Thank you!

 

[cpark4]

Also, now the same thing except instead of finding the percentage of times before 2 PM, now how do you find the percentage of times between 7AM-5PM?

TIA

 

[Dutchy]

45 is the number of entries in your list, so you might need to change that if the number changes.

I checked it manually and it seems to work on your sample. 27 times are before 1400 giving you a percentage of 27*100/45= 60

 

[cpark4]

Excellent, I'll be sure to change that value if there are additions! TY, TY, TY.

 

[Dutchy]

=(COUNTIF(A1:A45,"<17:00")-COUNTIF(A1:A45,"<07:00"))/45

 

[cpark4]

Very cool.

Would I reverse the order if I wanted to find % after 5PM and before 7AM?

like: =(COUNTIF(d2:d46,"<07:00")-COUNTIF(d2:d46,"<17:00"))/45

 

[cpark4]

right now =(COUNTIF(D2:D46,"<07:00")-COUNTIF(D2:D46,"<17:00"))/45 is giving me a negative result

-13.33%

 

[Domenic]

Try the following...

=SUMPRODUCT((D2:D46>"5:00 PM"+0)+(D2:D46<"7:00 AM"+0))/COUNT(D2:D46)

Hope this helps!