# stats % before a certain time

#### cpark4

##### New Member
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

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
something like

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

formatted as percentage

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!

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

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

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

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

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

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

-13.33%

Try the following...

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

Hope this helps!

Replies
4
Views
113
Replies
10
Views
404
Replies
3
Views
206
Replies
8
Views
331
Replies
1
Views
219

1,206,830
Messages
6,075,113
Members
446,123
Latest member
junkyardforme

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back