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

[hedgeman50]

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

 

[hedgeman50]

I have this equation for three sets of data in my spreadsheet. I just realized it's simply taking the first zero value in the column for each equation. That is, the 23 result I posted above represents the count from my value in $E4 to the FIRST zero value in the column--instead the first zero to precede the cell value in $E$4 (E28 in this case). The result (as you can see from the data posted) should be two--i.e., the first value of zero appeared two cells above E28.

Any ideas?

Thanks!

Max

 

[hedgeman50]

Success! Thanks to both Sean and Yogi! I ended up going with the equations Sean entered, as I have many values of zero (as opposed to just one before the minimum figure). Also, though I doubt the lowest figure will ever be repeated, it's nice to have that functionality built-in.

You guys are Excel wizards. Thanks again. If you're ever in Manhattan Beach (California), lunch is on me.

Max

 

[Yogi Anand]

Hi Max:

The formulation that I had provided earlier for the Backward Count will work if there was only one zero (0) in the Backward Count range -- otherwise for the Backward Count we have to do some additional work as shown in the following illustration ...

Book2
	D	E	F	G	H	I	J
4	Intermediary Calculation
5	-0.0468	0.00%
6	-0.0256	0.00%
7	0	0.00%
8	-0.0133	0.00%
9	0	-2.84%				-0.0468	28
10	0	0.00%			Backward Count	2
11	0	0.00%			Forward Count	1
12	-0.0053	0.00%
13	-0.0235	-1.37%
14	0	0.00%
15	0	0.00%
16	0	0.00%
17	0	0.00%
18	0	0.00%
19	0	0.00%
20	-0.0137	-2.35%
21	0	-0.53%
22	0	0.00%
23	0	0.00%
24	-0.0284	0.00%
25	0	-1.33%
26	0	0.00%
27	0	-2.56%
28	0	-4.68%
Calculation Data

The additional formula in cells D5:D28 in the illustration is ...

=INDEX(E:E,$J$9+1-ROW(INDIRECT("1:"&$J$9)))

This is an array formula and is to be entered with CTRL+SHIFT+ENTER rather than with just ENTER.

I am going to look at simplifying this and see if we can avoid having to use thois additional column and the additional calculation.

I hope this helps.

 

[Yogi Anand]

Hi Max:

Here is one without having to use the additional column and the associated calculation ...

Book2
	E	F	G	H	I	J
1
2
3
4
5	0.00%
6	0.00%
7	0.00%
8	0.00%
9	-2.84%				-0.0468	28
10	0.00%			Backward Count	2
11	0.00%			Forward Count	1
12	0.00%
13	-1.37%
14	0.00%
15	0.00%
16	0.00%
17	0.00%
18	0.00%
19	0.00%
20	-2.35%
21	-0.53%
22	0.00%
23	0.00%
24	0.00%
25	-1.33%
26	0.00%
27	-2.56%
28	-4.68%
Calculation Data (2)

The formula in cell I10 is ... =$J$9-MAX(IF(INDIRECT("E5:E"&$J$9)=0,ROW(INDIRECT("5:"&$J$9))))

This is an array formula and is to be entered with CTRL+SHIFT+ENTER rather than with just ENTER.

Let me know how it works out when you put this formulation through various test runs.

 

[hedgeman50]

Yogi,

Your persistence knows no bounds. I like that.

Do you see any problem with Sean's equation? I have entered for the countback function:

{='Calculation data'!E4-LARGE(ROW('Calculation data'!E5:INDEX('Calculation data'!E:E,'Calculation data'!E4))*('Calculation data'!E5:INDEX('Calculation data'!E:E,'Calculation data'!E4)=0),1)}

(Sorry about the messiness of the references to another worksheet.)

It produces the correct results. Do you foresee a time when it wouldn't?

Max

 

[Yogi Anand]

Yogi,
....
Do you see any problem with Sean's equation? I have entered for the countback function:

{='Calculation data'!E4-LARGE(ROW('Calculation data'!E5:INDEX('Calculation data'!E:E,'Calculation data'!E4))*('Calculation data'!E5:INDEX('Calculation data'!E:E,'Calculation data'!E4)=0),1)}

(Sorry about the messiness of the references to another worksheet.)

I have not worked with Sean's equation that you have posted here.

It produces the correct results.

That is what counts!

Do you foresee a time when it wouldn't?

Sorry -- wouldn't know!

Good Luck with your project!

 

[hedgeman50]

Thanks again for all your assistance!

Max