# Help! I need a "count until" function...

#### hedgeman50

##### Board Regular
Here's the setup: I have data in column A (A5:A64, to be exact). Column A has negative numbers and multiple zero values. In cell E19, I have: =MIN(A5:A64).

I need for cell E20 to be able to count back from the cell that had the min value from the equation in E19 (cell A28 in this case, but the cell could change when data is updated, so I don't want to hard-code that cell) to the most recent zero value (counting backward). I also need for the next cell, E21, to be a count of the number of cells after the result from the equation in E19 until a value of zero is reached (same as previous equation but counting forward instead of backward).

The equation would look something like this (but using correct formulas):

=count backward from (cell result in E19) until 0.00 is reached

How can this be done?

Thanks,
Hedgeman50

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Yogi Anand

##### MrExcel MVP
Hi Hedgman50:

I don't know if I got the drift of your Backward and Forward count correctly -- in any event, I hope you find the following to be of some interest ...
Book2
ABCDEF
53
62
75
87
95-514
102Backward Count3
110Forward Count2
12-2
138
14-5
159
160
170
18-4
191
203
217
2211
236
242
Sheet5

#### hedgeman50

##### Board Regular
Yogi,

I'm still trying to understand the equations, but they are exactly what I needed. You're brilliant! Thank you very much.

Best regards,
Max

#### Yogi Anand

##### MrExcel MVP
hedgeman50 said:
Yogi,

I'm still trying to understand the equations, but they are exactly what I needed.
....
Best regards,
Max
Hi Max:

Are you all set now? -- if you do need to discuss this further, please post back -- and then let us take it from there.

#### howzat

##### Board Regular
Hi max,

I'm sure that if you had indicated the need for what follows, then I'm sure Yogi would have given you a solution 10 times faster (at least) than little ol me. But anyway, assuming you wish to continue entering data into column A, i found 2 scenarios that would probably cause problems:

1) if you enter another amount further down that is equal to a previously entered minimum in this case "-5", then your current formula's will return the result of initial minimum amount, and not the most recent.

2) If you enter a new minimum, the backward count will count from the new minimum back to the first entered "0", and not the most recent, therefore exaggerating the amount.

if you are interested, here is a solution:
Min.xls
ABCDEF
5
62
75
87
95-525
102Backward4
115Forward2
12-2
138
14-5
159
160
170
18-4
19-5
203
210
2211
23-3
242
25-5
267
270
280
29
Sheet1

Please note that formula's in cells: E11 and F9 are array entered.

ie, hit Ctrl+Shift+Enter to enter the formula, and not just enter.

Also note that my data is not exactly in line with Yogi's post, but the formula's need not be altered. They do however now analyse down to cell A100.

#### howzat

##### Board Regular
howzat said:
Please note that formula's in cells: E11 and F9 are array entered.

ie, hit Ctrl+Shift+Enter to enter the formula, and not just enter.

quote]

Opps, its E10 that's array entered and not E11

#### hedgeman50

##### Board Regular
Thank you, Sean. I'm happy to say that there will never be a duplicate negative value because the decimal places go out far too much. Thanks again, though, for the thought.

I'm taking this one step at a time.

Yogi or Sean: Here is the formula I have to determine the row of the lowest value: =MATCH('Statistical analysis'!\$E\$19,\$E:\$E,0). I would like to place this formula in a cell near the top of Column E (where the data is). This, however, creates a circular reference. If I change the equation to =MATCH('Statistical analysis'!\$E\$19,\$E\$5:\$E\$100,0), then it returns a figure that is too low by 4 (as it starts in E5) and won't calculate beyond E100. Is there a way to keep the equation in Column E and have it go down to as low as the data is entered?

Thank you.

Max

#### hedgeman50

##### Board Regular
So here's how I entered the formula to get rid of the circular reference:

=MATCH('Statistical analysis'!\$E\$19,\$E\$5:\$E100,0)+4

Is this what you guys would suggest?

If so, now I just need to know how to replace \$E100 with something to make it go from \$E\$5 through the end of column e (before moving on to the next step).

Thank you!

Max

Hi Max:

That would do!

#### hedgeman50

##### Board Regular
Yogi:

I'm a bit perplexed now. I have as the equation for the countback cell:

='Calculation data'!\$E\$4-4-MATCH(0,'Calculation data'!\$E\$5:INDEX('Calculation data'!\$E:\$E,'Calculation data'!\$E\$4),0)

I'm getting 23 for the result, though, and I can clearly see that it should be 2. I was very careful to replace every item in your equation with the actual locations in my own spreadsheet.

Any ideas?

Thanks!

Max

On the 'Calculation data' worksheet, Column E looks like this:

E1-blank
E2-blank
"Drawdown"
28 [this is the location of the lowest figure in Column E]
0.00%
0.00%
0.00%
0.00%
-2.84%
0.00%
0.00%
0.00%
-1.37%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
-2.35%
-0.53%
0.00%
0.00%
0.00%
-1.33%
0.00%
-2.56%
-4.68%
0.00%
-2.14%
-1.99%
0.00%
0.00%
-1.14%
0.00%
-1.25%
-3.36%
0.00%
0.00%
0.00%
-1.05%
-2.18%
-0.06%
-2.09%
-0.44%
-2.46%
-0.69%
0.00%
-2.78%
0.00%
0.00%
-3.30%
-2.14%
-1.78%
-0.94%
0.00%
0.00%
0.00%
0.00%
0.00%
-0.60%
0.00%
0.00%
0.00%

Replies
11
Views
510
Replies
2
Views
288
Replies
16
Views
298
Replies
0
Views
51
Replies
6
Views
1K

1,187,175
Messages
5,962,044
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.

### Which adblocker are you using?

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

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