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

#### Diamond Dave

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

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Diamond Dave

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

Last edited:

#### Diamond Dave

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

##### Well-known Member
I'm not totally clear... what would be the answer in this case?
Excel Workbook
ABCDE
811/2/2010
821/3/2010
831/4/2010
841/5/2010
851/6/20101
861/7/20101
871/8/20103
881/9/20105
891/10/20108
901/11/20101514
Sheet1
Excel 2003

#### Diamond Dave

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

Last edited:

#### taigovinda

##### Well-known Member
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

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

##### Well-known Member
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

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

Replies
10
Views
669
Replies
3
Views
938
Replies
3
Views
696
Replies
7
Views
388
Replies
3
Views
305

1,187,179
Messages
5,962,056
Members
438,578
Latest member
MrJimC

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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