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

Diamond Dave

New Member
Joined
Jan 9, 2010
Messages
7
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Diamond Dave

New Member
Joined
Jan 9, 2010
Messages
7
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
Joined
Jan 9, 2010
Messages
7
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.
:confused:
 

taigovinda

Well-known Member
Joined
Mar 28, 2007
Messages
2,639

ADVERTISEMENT

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
Joined
Jan 9, 2010
Messages
7
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
Joined
Mar 28, 2007
Messages
2,639

ADVERTISEMENT

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
Joined
Jan 9, 2010
Messages
7
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
Joined
Mar 28, 2007
Messages
2,639
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
Joined
Jan 9, 2010
Messages
7
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,194
Messages
5,768,775
Members
425,492
Latest member
blueexcel123

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?

Disable AdBlock

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
Top