Averaging every 10th cell in a column

[Pogie58]

How do I tell Excel to average every 10th cell in a column.

The formulas I am currently using are below but they are not working because it appears the groups overlap.

=AVERAGE(IF(MOD(ROW('Survey Input'!G$2:G$2012)-ROW('Survey Input'!G$2),10)=0,'Survey Input'!G$2:G$2012,0))

=AVERAGE(IF(MOD(ROW('Survey Input'!G$5:G$2015)-ROW('Survey Input'!G$5),10)=0,'Survey Input'!G$5:G$2015,0))

=AVERAGE(IF(MOD(ROW('Survey Input'!G$8:G$2018)-ROW('Survey Input'!G$8),10)=0,'Survey Input'!G$8:G$2018,0))


Help ASAP

 

[AlphaFrog]

Here's one way...

<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td ></td><td >Formula</td></tr><tr><td ></td><td >{=AVERAGE(IF<span style=' color:008000; '>(INT<span style=' color:#0000ff; '>(<span style=' color:#ff0000; '>(ROW<span style=' color:#804000; '>(G$2:G$2012)</span>+8)</span>/10)</span>=<span style=' color:#0000ff; '>(ROW<span style=' color:#ff0000; '>(G$2:G$2012)</span>+8)</span>/10,'Survey Input'!G$2:G$2012,"")</span>)}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Formula Array:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Produce enclosing </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> by entering<br />formula with CTRL+SHIFT+ENTER!</span></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>

 

[delaneyjm]

Looks like AlphaFrog beat me to it...

Here's a non array solution for you.

In an empty cell (for example, C1), enter the value 10.

In an empty cell (for example, D1), enter the value "Criteria"
In an empty cell (for example, D2), just enter: =MOD(ROW(A2),$C$1)=0

In an empty cell (for example, E1), just enter
=DAVERAGE(G$2:G$2012,1,$E$1:$E$2)

 

[Scott Huish]

=AVERAGE(IF(MOD(ROW('Survey Input'!G2:G2012)-ROW(G2),10)=0,IF(ISNUMBER('Survey Input'!G2:G2012),'Survey Input'!G2:G2012)))

Confirm with CTRL-SHIFT-ENTER. This will average only for numbers that are entered in the appropriate rows.

If you want to average as if blanks are zeros:

=AVERAGE(IF(MOD(ROW('Survey Input'!G2:G2012)-ROW(G2),10)=0,'Survey Input'!G2:G2012))

Confirm with CTRL-SHIFT-ENTER

 

[Pogie58]

Thank you it worked.