SumIf (possible VBA) question

[Rammy]

Hi All,

I need a function (or VBA code) that will perform the following average:

1
2
5
0
1
0
0

I need to check if the last cell in the column is 0. If not, check the second to last cell. If that is not 0, check the next. If the next value is nonzero, take the average of all values including the nonzero value, and include 0 values that occur after that (all but the first value and the length of each column will be arbitrary).
For example, in the above example I would get (1+0+5+2)/4=8/4=2.

I imagine that one way of going about this would be reversing the order of the cells, and then taking average only after the first nonzero value. After the first nonzero value, however, 0s could be averaged. Maybe there is a more speed efficient way...

Any help would be much appreciated,

Thanks!

 

[schielrn]

The part I am a little confused on is the eliminating the first cell, wouldn't you know not to include that in your range, but maybe I am missing something. This will average to the last row that has a non-zero value and I just started at the row below the first cell. Let me know what I am missing or some other sample data sets and expected results?

Excel Workbook
	A	B	C
8	1		2
9	2
10	5
11	0
12	1
13	0
14	0
15
16
Sheet1

#VALUE!

And there may be better ways to do this, once I know that, but this is a start. Also it cannot be used on version prior to 2007, so let me know what version of Excel you are using as well.

Hope that helps.

 

[Rammy]

Sorry I didn't mention it, I am using Excel 2003.

 

[schielrn]

Then you can't use whole ranges in an array formula (control+shift+enter)

This should work:

=AVERAGE(INDIRECT("A9:A"&MAX(IF($A$1:$A$65000 < > 0,ROW($A$1:$A$65000)))))

Its basically finding the max row that is not = 0 and using that as the last row in the average range.

Reduce the 65,000 number if your data will not get anywhere near that number, as it will make the formula a little quicker.