Calculating the Average of Every Nth Value, Excluding Zeros

Hi,

I want to find average of every nth value, excluding zeros for which I found the solution through net as "=AVERAGE(IF((MOD(COLUMN(AE2:BI2)-CELL("col",AE2)-32,4)=0)*(AE2:BI2<>0),AE2:BI2))". This worked for range AE2:BI2 but when I copied the formula for the next result cell i.e., for range AF2:BJ2 it worked but I am not able to understand what is the role of -CELL("col",AE2)-32,4)=0) where I changed -32 to -33 for AF2:BJ2 range it did not work. This is i presume no. of cells difference from start cell. However it works for both the ranges with the same -32 value.

Please clarify.

regards,

T. Saravana

CELL("col",AE2) returns the column the cell is in 4 for anything in column d for example

mod(column(ae2:bi2)-cell ("col",ae2)-32,4) works out the difference between the starting column and the colum you are in and works out the remainder when it has been divided by 4. why the 32 is in it is a mystery as 32 is divisible by 4

if it is easier you could try a sumproduct which has the advantage of not being entered as an array

=SUMPRODUCT((MOD(COLUMN(AE2:BI2),4)=1)*(AE2:BI2)*(AE2:BI2>0))/SUMPRODUCT((MOD(COLUMN(AE2:BI21),4)=1)*(AE2:BI21>0))

It assumes there will be at least one values so no error trap is writen. the red values may need to be 0, 1, 2, or 3 depending which column you start in
eg if you start in AE it is the 31st column and you wanted to count this value the red 1 would be a 3 as 31/4 leaves a remainder of 3

Regards

Dav