Interesting, maybe unanswerable CountIf Question?

[Mike Slattery]

I have two columns:

The first is the volume for a stock and the second is that stocks price at that volume when it touched a support or resistance level.

Volume Turning Point Price
9795000 520.03
9826700 0
12596300 0
22707400 0
19658600 0
12064000 0
13741600 531.91
11204400 0
19073700 0
14218100 0
10376500 0
9056900 504.5
8967900 0
11431200 0
9353500 496.04

I need a solution for producing the Average Volume between the stock price numbers that are not zeros. As you can see the range is variable, so I have not been able to figure out how to utilize the most obvious solutions, i.e., CountIf or SumIf as I can not enter a range of cells?


Grateful for any ideas?

Thanks Michael

 

[Fazza]

hi, Mike

What about adding another column with a counter for cumulative count of values > 1, then make a pivot table showing average volume against this count? Is that OK?

regards

 

[Pup Denab]

Hi Mike
I'm not sure what you need, however here are 2 ways of getting the average stock and 2 ways of getting the average price
Stock
[TABLE="width: 388"]
<colgroup><col></colgroup><tbody>[TR]
[TD]=SUMIF(B2:B16,">1",A2:A16)/COUNTIF(B2:B16,">1")[/TD]
[/TR]
[TR]
[TD]=AVERAGEIF(B2:B16,">1",A2:A16)[/TD]
[/TR]
</tbody>[/TABLE]
Price
[TABLE="width: 360"]
<colgroup><col></colgroup><tbody>[TR]
[TD]=SUMIF(B2:B16,">1",B2:B16)/COUNTIF(B2:B16,">1")[/TD]
[/TR]
[TR]
[TD]=AVERAGEIF(B2:B16,">1",B2:B16)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mike Slattery]

Sorry if the goal was not clear. I need to obtain an average daily volume for the days between the numbers greater than zero and including the numbers greater than zero. So in the original example the first seven volumes would be added and divided by seven. The two prices greater and zero provide inclusive bookends that define the volume cells that become the average range.

9795000 520.03
9826700 0
12596300 0
22707400 0
19658600 0
12064000 0
13741600 531.91

The prices that are not zeros are either the price at a stocks top at resistance or a bottom at support. Since these are stock prices I have no way of pre-defining what the range between the numbers greater than zero will be.

Hope that this is helpful,

Thanks again, Michael

 

[Pup Denab]

OK Mike
A little bit harder than I originally thought, but give this a try
In C2
=IF(B2>COUNTIF($B$1:B2,">0"),IF(ISEVEN(COUNTIF($B$1:B2,">0")),COUNTIF($B$1:B2,">0")-1,COUNTIF($B$1:B2,">0")),COUNTIF($B$1:B2,">0"))
In D2
=AVERAGEIF($C$2:$C$21,C2,$B$2:$B$21)

Hopefully this will give you what you need, just fill the formula down, you will probably need to adjust the range, but it should work

 

[Mike Slattery]

Hello Pup Denab:

Thanks you for your imaginative, insightful and inspired solution to my problem. It's most of the way home and I believe the difference is your lack of complete information. It would probably work perfectly if there were only four columns in the spreadsheet and only few days of historical data.

Is there a method to get a complete worksheet to you?

I am very very grateful for your assistance. You are indeed an excel expert!

Thanks, Michael

 

[bschwartz]

Try posting to box.com or other file sharing site, and enable the uploaded file to be shared with public via URL.

 

[Pup Denab]

HI Mike
Ben is correct, if you could upload to dropbox (it free), I have tested further to 1000 lines of random data, and based on the criteria it still works fine, ll you need do is change the formula to cover your data i.e.
=AVERAGEIF($C$2:$C$1000,C2,$B$2:$B$1000)
it will also work if you make the range bigger than your data

when you upload you file, make sure that any senitive data, is not in their, i.e. company names ect, just use a b c ect


the reason to upload is that their may well be a better solution and their are a whole gang of cool experts here who can then help

 

[Mike Slattery]

Hi Denab:

I got it working, you were correct. I had not changed the range to include the entire volume of cells utilized from the download.

If anyone would like to look at this spreadsheet when it is complete or at least presentable, I will be glad to post it.

I have been working in excel for many years and consider myself pretty proficient. I would never have solved this problem without your assistance and again I am grateful.

Thanks, Michael

 

[MikeWx]

If you are still interested in a one column solution, enter the following array formula (CTL SHIFT ENTER instead of ENTER) in Cell C3 and copy it down. It will give you the average you are looking for at each non-zero value in Column B.

=IF(B3=0,"",AVERAGE(OFFSET(A3,SUM(-(ROW(A3)-MAX(IF($B$2:B2>0,ROW($B$2:B2))))),0,SUM(ROW(A3)+1-MAX(IF($B$2:B2>0,ROW($B$2:B2)))))))