Average, Countif

[Guest]

Need to average a column of numbers, except I only need to use every 7th row.
Like row 3,10,17......640. In addition not all of these cells are populated.
First is it possible to create a formula that would only use every 7th row?
Second how can I insure an accurate average by only counting populated cells?

Thanks,

M.A.Tyler.

 

[Guest]

Hi,

the question is that your first numbet is in the third row but you want each
7th row????
but i guess you mean after the row 3 every 7 row, so you can do it by a
helper column:
helper column=B original column=A
in the third row of helper column enter: =A3
in the 4th row of helper column enter:
=IF(INT((ROW()-3)/7)=(ROW()-3)/7,A4,0)
copy drag the formula to end of your data e.i.: the row 100
in the row 101 of helper column enter :
=COUNTIF(B3:B100,">0")
in the row 102 of helper column enter:
=ROUND(SUM(B3:B100)/B101,2)

Thanks,

 

[T. Valko]

Try this array formula**

=AVERAGE(IF(MOD(ROW(A3:A640),7)=3,A3:A640))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Average will ignore empty cells.

Biff

 

[Guest]

Try this:

=AVERAGE(IF((MOD(ROW(A3:A640),7)=3)*(A3:A640<>""),A3:A640))

ctrl+shift+enter, not just enter

 

[Guest]

Your formula "failed"

Assume A3=3, A10=29, A17=99, A24=22
your formula returns 1.66404 (should be 38.25)

If A24="" (your formula returns 37.6667, should be 43.66667)

 

[T. Valko]

=AVERAGE(IF(MOD(ROW(A3:A640),7)=3,A3:A640))

Average will ignore empty cells.

But not when used in an IF array.

See TM's reply.

Biff

 

[Guest]

@Farhad - your formula stops working if any of the input values are actually
zeroes, as you are not counting them
You could modify the IF to return "" instead of 0 and then just use a
regular AVERAGE function.

As for these smart-alec array functions, they're just too clever for words!

 

[Guest]

TM-
This works well thanks for the help, Just a couple more things. The actual
data range has increased, so it's really A3:A646. Also how can I copy and
paste this array so if I would like the data stored in the 7th row but
counting down from the 4th row (from the top of the range) instead of the 3rd
and so on... I've gotten it to work down to starting from the 6th row, but
the 7th row down from the 7th row from the top is giving me a division error?

I hope the question makes sense. In other words if I start with your
original array formula in A1 and copy it down thru A5.

Can you help?

 

[Guest]

Sorry the one thats giving me trouble is the 7th row down from the top of the
range.