Trying to find percentage of winners and losers

[alexm3430]

Below is a sample of data that i want to see on a percentage basis how many times a certain hour is positive or negative over last 30 days or however many days i plug in. I want to see each hour independently.

Thank you.

Day	Year	Month	Dat 2	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24
Sat	2011	Jan	1/1/2011	1.60	1.90	2.67	3.00	3.00	5.64	8.78	10.22	9.32	6.91	4.83	3.43	1.99	2.90	2.12	1.83	2.85	7.48	10.44	4.91	(6.63)	0.15	1.23	0.62
Sun	2011	Jan	1/2/2011	(1.90)	(1.53)	(1.19)	(1.38)	(1.20)	5.55	4.51	4.24	(12.07)	(1.72)	1.00	1.27	0.39	0.73	0.84	1.13	1.96	3.87	9.74	8.76	7.62	8.96	7.39	8.58
Mon	2011	Jan	1/3/2011	4.15	2.81	3.33	3.81	5.78	9.93	18.10	24.14	13.24	8.50	5.80	7.27	6.46	5.16	4.22	4.17	2.84	5.26	19.44	15.63	11.75	12.82	12.37	11.54
Tue	2011	Jan	1/4/2011	2.16	4.08	4.53	0.09	(0.57)	(2.26)	0.44	(0.18)	4.26	(5.94)	(7.60)	(5.39)	(2.48)	(3.27)	(2.79)	(2.08)	(2.92)	5.44	10.70	4.36	2.28	3.41	5.17	6.64
Wed	2011	Jan	1/5/2011	0.97	0.95	1.87	1.39	0.05	4.46	(414.05)	(210.23)	7.51	(0.16)	(16.08)	(8.86)	(2.29)	(0.30)	(1.17)	(2.65)	(3.09)	1.55	(339.75)	(1.57)	(8.13)	(5.90)	(11.95)	(10.25)
Thur	2011	Jan	1/6/2011	(7.26)	(5.97)	(4.46)	(7.73)	(609.35)	0.16	(1.80)	1.54	3.83	(0.48)	(1.78)	(0.62)	1.06	0.13	1.74	2.17	2.13	5.37	11.11	4.15	0.36	(1.02)	(3.83)	2.99
Fri	2011	Jan	1/7/2011	2.34	1.42	0.56	1.44	7.37	8.82	(1046.96)	13.85	9.50	5.42	1.92	1.94	1.46	0.41	1.18	1.71	4.05	10.01	22.46	17.60	10.08	9.07	8.83	7.50
Sat	2011	Jan	1/8/2011	5.00	3.11	2.55	1.36	4.33	4.61	8.08	10.11	4.87	4.64	4.01	2.51	2.72	4.64	4.99	6.05	5.68	11.64	22.99	13.81	10.92	9.03	10.73	12.53
Sun	2011	Jan	1/9/2011	10.13	6.55	8.05	8.35	6.51	5.89	12.19	15.41	11.74	11.54	9.78	3.86	(0.12)	(1.78)	(0.62)	0.25	2.81	12.60	25.05	17.95	15.92	13.72	13.38	12.88
Mon	2011	Jan	1/10/2011	4.45	4.16	4.15	4.02	9.55	11.96	21.99	21.79	9.54	6.89	2.70	0.26	(2.82)	(4.23)	(4.84)	(4.10)	(0.75)	8.98	29.66	15.31	13.72	11.19	9.63	13.15
Tue	2011	Jan	1/11/2011	1.83	2.26	1.42	(1.34)	(0.93)	(0.52)	6.22	(17.33)	2.24	(3.46)	(5.73)	(7.75)	(5.34)	(3.98)	(2.32)	(1.34)	(1.72)	5.85	16.25	4.95	2.43	5.00	2.82	3.61
Wed	2011	Jan	1/12/2011	(2.97)	(1.61)	(1.58)	(1.42)	2.24	7.90	33.80	24.63	13.57	8.38	4.80	3.91	2.52	1.38	0.26	0.30	(1.37)	1.77	16.19	9.01	5.77	4.55	1.52	5.58
Thur	2011	Jan	1/13/2011	2.56	1.91	1.87	3.51	5.87	9.50	35.50	34.06	14.22	7.71	5.02	4.34	0.84	(1.07)	(0.30)	(1.61)	(2.53)	0.62	9.40	7.93	6.41	5.01	2.23	3.51
Fri	2011	Jan	1/14/2011	0.84	0.72	0.97	1.04	3.36	8.30	28.03	26.69	12.69	5.99	(0.32)	(1.72)	(2.62)	(1.08)	0.13	(1.53)	(1.22)	4.53	14.29	8.45	4.03	4.54	(1.18)	1.59
Sat	2011	Jan	1/15/2011	0.30	(1.19)	(0.54)	(0.61)	(1.33)	0.25	10.98	7.11	4.00	(12.05)	(9.26)	(6.21)	(4.00)	(4.58)	(2.35)	(1.16)	0.18	1.28	14.32	5.14	1.06	0.71	(22.49)	(4.05)
Sun	2011	Jan	1/16/2011	(4.05)	(5.56)	(3.61)	(4.23)	(3.91)	(1.10)	8.25	8.33	11.46	6.36	(3.62)	(13.15)	(28.00)	(15.69)	(13.21)	(11.15)	(6.53)	(1.24)	14.38	12.76	6.27	2.38	0.20	(4.40)
Mon	2011	Jan	1/17/2011	(4.49)	(0.23)	2.58	2.08	2.40	8.07	19.28	20.28	15.69	3.09	(5.30)	(6.84)	(2.25)	1.73	4.09	4.29	5.28	7.00	19.69	7.25	5.54	6.26	5.74	7.56
Tue	2011	Jan	1/18/2011	5.39	6.25	4.78	2.84	4.90	10.33	21.23	22.34	11.93	3.96	3.91	2.50	2.56	2.96	3.04	3.67	3.25	6.04	17.88	6.85	4.66	3.86	6.24	7.29
Wed	2011	Jan	1/19/2011	5.18	3.62	2.71	2.92	4.28	5.83	18.33	18.69	10.93	8.05	8.49	8.13	8.29	6.72	6.96	5.71	5.29	9.25	24.67	6.96	6.02	6.53	8.53	8.12
Thur	2011	Jan	1/20/2011	2.84	3.80	3.36	3.31	4.94	5.72	11.22	7.91	3.73	1.29	(111.08)	2.39	2.14	3.01	1.60	4.01	2.51	6.23	(1.98)	(5.45)	(49.96)	(5.49)	(1.50)	0.70
Fri	2011	Jan	1/21/2011	(4.88)	(1.55)	0.48	3.21	7.35	11.45	28.09	29.19	18.89	12.00	5.99	4.38	2.52	3.12	6.09	8.19	8.79	14.71	27.52	16.38	10.10	7.29	(4.92)	(5.35)
Sat	2011	Jan	1/22/2011	(50.93)	(15.68)	(8.49)	(16.08)	(3.81)	(0.74)	(7.42)	2.13	11.79	7.83	5.82	6.61	6.69	7.49	8.23	10.28	10.69	11.84	22.57	13.87	11.28	11.40	9.97	11.44
Sun	2011	Jan	1/23/2011	4.28	0.51	(0.20)	3.89	4.78	7.77	16.11	21.23	23.54	22.40	13.56	8.93	6.59	2.00	(1.78)	(4.34)	(2.71)	(1.96)	(10.66)	0.50	0.97	0.85	(0.18)	4.48
Mon	2011	Jan	1/24/2011	1.99	2.43	3.37	3.25	3.36	2.10	12.41	18.10	12.42	6.14	2.15	1.01	1.82	2.26	2.36	2.08	1.34	5.26	20.04	14.42	10.83	6.28	4.41	10.65
Tue	2011	Jan	1/25/2011	5.68	4.03	4.40	4.00	3.42	3.19	(44.21)	(93.72)	5.83	3.07	(0.86)	(1.02)	1.46	2.13	3.12	6.04	7.06	8.32	20.83	10.36	7.47	(0.02)	0.72	4.85
Wed	2011	Jan	1/26/2011	(0.93)	(2.25)	(1.44)	0.15	0.79	5.72	24.33	27.40	7.50	4.22	4.46	2.48	2.67	1.55	2.11	2.79	2.98	6.57	16.79	6.30	5.70	2.94	(3.30)	3.41
Thur	2011	Jan	1/27/2011	(1.13)	(1.66)	(2.23)	(2.12)	(0.67)	(8.53)	(776.99)	(4.96)	6.85	2.49	3.02	2.87	2.34	3.33	4.21	4.08	3.93	6.54	16.25	8.47	9.14	10.82	12.96	13.50
Fri	2011	Jan	1/28/2011	4.91	5.07	3.79	4.04	5.82	7.02	29.68	23.22	7.45	4.18	2.19	1.55	2.34	2.57	2.51	2.44	3.47	7.91	23.83	9.32	8.68	9.20	12.70	19.74
Sat	2011	Jan	1/29/2011	8.92	10.20	9.32	9.08	9.44	11.47	9.98	9.72	17.23	11.61	4.70	4.49	4.56	5.96	7.18	6.62	4.10	6.99	17.93	8.71	7.74	7.69	12.34	12.11
Sun	2011	Jan	1/30/2011	3.41	3.70	10.39	14.53	8.57	5.26	3.62	7.59	6.41	7.83	5.89	(1.26)	(7.30)	(10.08)	(4.42)	(3.38)	(0.72)	4.90	25.12	10.36	0.91	4.05	6.37	4.72
Mon	2011	Jan	1/31/2011	2.36	3.53	6.09	5.75	5.40	4.86	16.78	12.63	9.73	5.44	(4.05)	0.68	2.34	3.32	4.01	4.80	7.69	12.43	30.45	11.86	10.25	9.52	13.01	14.11

<colgroup><col><col><col><col><col span="24"></colgroup><tbody>
</tbody>

 

[tybaltlives]

hope this is what you are looking for. I only show a portion of your table but the range extends over all of January. The formulas may be copied to the right for each hour:

Excel 2010
	A	B	C	D	E	F	G
6				Positive	71.0%	67.7%	71.0%
7				negative	29.0%	32.3%	29.0%
8
9	Day	Year	Month	Dat 2	1	2	3
10	Sat	2011	Jan	1/1/2011	1.6	1.9	2.67
11	Sun	2011	Jan	1/2/2011	-1.9	-1.53	-1.19
12	Mon	2011	Jan	1/3/2011	4.15	2.81	3.33
13	Tue	2011	Jan	1/4/2011	2.16	4.08	4.53
14	Wed	2011	Jan	1/5/2011	0.97	0.95	1.87
15	Thur	2011	Jan	1/6/2011	-7.26	-5.97	-4.46
Sheet1

Cell Formulas
Range	Formula
E6	=1-E7
E7	=COUNTIF(E10:E40,"<"&0)/COUNT(E10:E40)

 

[alexm3430]

This is exactly what I needed thank you. What if I only wanted to see just what Mondays do?

 

[tybaltlives]

I can tell you but I'm away from my computer and can't give you the formulas. Replace COUNT with the COUNTIFS function and add the criteria to count only if mon. Replace the SUM with SUMPRODUCT and include the day column to sum only rows with Monday in it. Would take me about 2 min to do but won't be back at a computer for a few days. I'm on vacation.

 

[tybaltlives]

Oh sorry. No SUMPRODUCT. Instead replace the count function in the denominator with COUNTIFS and only count if Monday's. I think you can figure this out!

 

[tybaltlives]

OK. Im back. Try this. You can select the day you want in cell C3.

Excel 2010
	A	B	C	D	E	F	G	H
3			Mon	Positive	80.0%	80.0%	100.0%	100.0%
4				negative	20.0%	20.0%	0.0%	0.0%
5
6			All	Positive	71.0%	67.7%	71.0%	74.2%
7				negative	29.0%	32.3%	29.0%	25.8%
8
9	Day	Year	Month	Dat 2	1	2	3	4
10	Sat	2011	Jan	1/1/2011	1.6	1.9	2.67	3
11	Sun	2011	Jan	1/2/2011	-1.9	-1.53	-1.19	-1.38
12	Mon	2011	Jan	1/3/2011	4.15	2.81	3.33	3.81
13	Tue	2011	Jan	1/4/2011	2.16	4.08	4.53	0.09
14	Wed	2011	Jan	1/5/2011	0.97	0.95	1.87	1.39
15	Thur	2011	Jan	1/6/2011	-7.26	-5.97	-4.46	-7.73
Sheet1

Cell Formulas
Range	Formula
E3	=COUNTIFS(E10:E40,">"&0,$A$10:$A$40,"="&$C$3)/COUNTIFS($A$10:$A$40,"="&$C$3)
E4	=COUNTIFS(E10:E40,"<"&0,$A$10:$A$40,"="&$C$3)/COUNTIFS($A$10:$A$40,"="&$C$3)
E6	=1-E7
E7	=COUNTIF(E10:E40,"<"&0)/COUNT(E10:E40)