How many cells does it take to exceed X, and stop counting

[Diamond Dave]

Ladies and gentlemen,
I am trying to compare a value ( say C80 ) and figure out how many cells it takes to exceed a value ( say -2 ) in a column ( say E79:E2 ) but then actually stopping the count after that first cell count has been triggered.
I'm in a bind - any help would be highly appreicated.
Dave

 

[taigovinda]

Welcome to the board!

Check out this thread, I think it is exactly what you want to do. Scroll to the bottom for the best answers. http://www.mrexcel.com/forum/showthread.php?t=433549

 

[Diamond Dave]

Thank you for the info. Unfortunately, I'm not good with the programming and I didn't seem to see what I was after. I have a small partial screen shot of what I am after but it will not let me put it in. I will try to restate it differently.
I have (daily high) prices in a vertical column from C2 to C200, and ( daily close ) prices in a vertical column from E2 to E200 with the date in the A column - all data is descending with the most recent date last.
I would take my reference value at cell C90. It will start looking at the E column going backward starting with E89 and count how many cells it takes to exceed a difference of 2, and then it will stop counting and return that value - such a 2, 10 , or 20. The idea behind it is easy but as I know, it is most likely not easy to pull off - especially for me. I can email a small gif screen capture with arrows if needed.
Thank you again....
Dave

 

[Diamond Dave]

It would almost be like this:
C90 is the beginning reference number
count how many cell it takes C90 to be greater than 2 by looking or comparing it to the range starting at E89 to E2. Once the value in C90 is exceeded by 2, return the value of how many cells it took to do so.

 

[taigovinda]

I'm not totally clear... what would be the answer in this case?

Excel Workbook
	A	B	C	D	E
81	1/2/2010
82	1/3/2010
83	1/4/2010
84	1/5/2010
85	1/6/2010				1
86	1/7/2010				1
87	1/8/2010				3
88	1/9/2010				5
89	1/10/2010				8
90	1/11/2010		15		14
Sheet1

Excel 2003

 

[Diamond Dave]

The answer should be 1 - the amount of cells it takes to go from 15 to 8 vertically. Since 15 is the reference, it ignore 14 which occurs on the same day. If it was to exceed 11, it would return 3.

 

[taigovinda]

Now I'm more confused. I expected the answer would be 4...

You said that C90 is the reference and you add until you exceed the reference by 2, right?

So...

C90 = 15
15+2 = 17
8+5+3+1 = 17

...how is it 1? We are not trying to find the minimum number of cells, starting with E89 and moving upwards, that will sum to 17 or more?

 

[Diamond Dave]

criteria is >2 points backwards or when does it exceed less than
2 points going up the close column. There only will be one answer
per row - they will be color coded.

Col Date Daily High Close Answer
84 6-May-09 38.77 37.76
85 7-May-09 40.14 39.58
86 8-May-09 41.57 41.30
87 11-May-09 41.01 40.55
88 12-May-09 41.48 40.67
89 13-May-09 40.78 39.94
90 14-May-09 40.30 6 (it exceeded 38.30)

Col Date Daily High Close Answer
84 6-May-09 38.77 37.76
85 7-May-09 40.14 39.58
86 8-May-09 41.57 41.30
87 11-May-09 41.01 40.55
88 12-May-09 41.48 40.67
89 13-May-09 40.78 39.94 5 (it was less than 38.78)
90 14-May-09 40.30

Col Date Daily High Close Answer
84 6-May-09 38.77 37.76
85 7-May-09 40.14 39.58
86 8-May-09 41.57 41.30 2 (it exceeded 39.57)
87 11-May-09 41.01 40.55
88 12-May-09 41.48 40.67
89 13-May-09 40.78 39.94
90 14-May-09 40.30

So the reference cell will always move down one with each advancing day but it will look backwards to see how many days it takes to exceed 2 less.

 

[taigovinda]

Ohhh... so you are looking for the first cell (on a preceding row, in the proper column) where that individual cell's value falls below (reference value minus 2)? And the columns are still C and E?

 

[Diamond Dave]

right. As each day unfolds, the new daily high is looking backwards to see how many days it could have taken to achieve the goal of over +2 for today. So I guess looking backwards it is then actually less than two. Sorry about the spacing in my columns collapsing, makes it harder to follow.