Survey Spreadsheet problem

[Pogie58]

I have a spreadsheet and I can not figure what I am doing wrong with the formula when I copy it to another range of cells.

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

When I moved it to the next group of cells it does not work, and I have a third group of cells to add it to. See below
=AVERAGE(IF(MOD(ROW('Survey Input'!G$5:G$2015)-ROW('Survey Input'!G$5),10)=0,'Survey Input'!G$5:G$2015,0))

The next group would be G8 G2018

What is happening is the question group 1-10 is calculating correctly but when I input data in question group 11-20 or 21-30 it changes the data in all three groups.

Help
<!-- / message -->

 

[DonkeyOte]

Assuming the formulae are being confirmed as Arrays (CTRL + SHIFT + ENTER) they will "work" in the sense that the syntax / adaptation is fine.

Regards the calculation itself - it may be that your discrepancies stem from the use of 0 in your IF which could be distorting your results ?

In essence >90% of your Array values are 0 in each calculation.
wherever the row iterated fails the MOD test 0 is added to the average array

Elaborating on the above - using your first formula as an example - your Array will comprise 2011 values:

-- 1815 fail the MOD test and are thus 0

-- 202 pass the MOD test (G2, G12, G22 etc...) and values are thus sourced from those cells directly (may/may not be numeric)


If you wish to average only those 202 values that satisfy your MOD test then you should dispense with the 0, e.g.:

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

By omitting the False argument of the IF rows that fail the MOD test will now populate the Array with a Boolean FALSE. These booleans will be ignored in the subsequent Average calculation.


It could well be the case that your use of 0 in the Average is entirely deliberate in which case disregard the above (obviously).