Help needed

[mrmike123]

Hi. First time posting here and hopefully someone will have the solution to my question. I have 2 columns of data. Column A has a 3 digit number, example, .468, .523, etc. In column B, it is either yes or no. Column A represents a numeric value for a test, and column B is whether the test was positive or negative. THe file has over 500 lines. What Im looking for is a way to find which range has the highest % of yes returns. Please see below. Since the file is 500 + lines, Id like to be able to find, lets say which 40 period range has the most yes values. THen Id like to be able to change the range to say 50 data points. Is there a way in solver, what if analysis or a formula which can be used which will allow a different # of ranges? Perhaps if the yes and no are changed to numberic values, that may also be something which would help. Thanks in advance to anyone willing to assist here.
Col A Col B
.456 NO
.485 Yes
.492 Yes
.496 Yes
.499 No

 

[KRice]

You might try something like this. I added a column A for an index if you want to later report the index associated with the max value.

Book1
	A	B	C	D	E	F
1	idx	value	outcome	#"Yes"	range -->	6
2	1	0.456	No		Max # Yes	5
3	2	0.485	Yes
4	3	0.492	Yes
5	4	0.496	Yes
6	5	0.499	No
7	6	0.487	Yes	4
8	7	0.491	Yes	5
9	8	0.499	Yes	5
10	9	0.485	No	4
11	10	0.477	No	3
12	11	0.499	No	3
13	12	0.499	No	2
14	13	0.485	Yes	2
15	14	0.48	No	1
16	15	0.499	No	1
17	16	0.467	Yes	2
18	17	0.499	Yes	3
19	18	0.453	No	3
20	19	0.495	No	2
21	20	0.499	Yes	3
Sheet2

Cell Formulas
Range	Formula
F2	F2	=MAX(D2:D21)
D2:D21	D2	=IF(A2<$F$1,"",COUNTIF(OFFSET(C2,,,-$F$1,),"Yes"))

 

[mrmike123]

Kirk: Thanks very much for the quick reply. This is what I was looking for. I have one other bit of info needed, but I have no issue getting that. It was the offset portion of what you posted that greatly helped. Appreciate it!!

 

[KRice]

You're welcome...glad to help. Welcome to the forum!