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

hedgeman50

Board Regular
Joined
Sep 20, 2002
Messages
76
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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
Joined
Sep 20, 2002
Messages
76
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
Joined
Mar 12, 2002
Messages
11,454
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
Joined
Oct 13, 2003
Messages
144

ADVERTISEMENT

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
Joined
Oct 13, 2003
Messages
144
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
Joined
Sep 20, 2002
Messages
76

ADVERTISEMENT

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
Joined
Sep 20, 2002
Messages
76
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
 

hedgeman50

Board Regular
Joined
Sep 20, 2002
Messages
76
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%
 
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,101
Messages
5,768,101
Members
425,453
Latest member
bince

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