Average of every nth row

Hi,

I have a dataset madeup of 6 columns.

The dataset has been filled where under each heading there are 25 entries representing different sampling timers. Under the 25th entry is another row of the same column headings.

I want to create a summary of the dataset where each entry is the average of average 27th cell. i.e. average of value at sample time 1 etc...

I have tried the following formula =AVERAGE(IF(MOD(ROW(A2:A1562),27)=0,A2:A1562))
adapted from http://www.atlaspm.com/toms-tutorial...very-nth-cell/

But i am getting the value 0 out every time?

Does anybody have an idea of where i am going wrong here?

Kind Regards,

Gray

Can you attach a sample book?

Maybe the values in column A are not real numbers, but numbers stored as text?

Also important, the formula needs to be enterd as an array by pressing CTRL + SHIFT + ENTER

Hi,

Cheers, entering it as an array is making the formula 'work'.

Problem is i think the formula is not correct for my purpose.
Formula seems to be skipping some values.


samplebook.xlsx



G

Try

=AVERAGEIF(A$1:A$79,"?*",A2:A80)

Originally Posted by Jonmo1

=AVERAGEIF(A$1:A$79,"?*",A2:A80)

I'm having one of those face meets palm moments for missing something so obvious

@jason

I wouldn't call it 'obvious'
It does require a little 'outside the box' thinking, and not fixating on the every nth thing.

Whenever I see a request for average/count/sum every nth row/column, I ALWAYS look for some sort of key that can be used instead.

Maybe not the most obvious of obvious things, Jonmo, but given how often I use the method of offsetting the ranges between the arguments of a function to achieve the desired result from misaligned data, I am a little disappointed with myself lol.

Even if I had seen it, not sure that I would have noticed the absolute row on the criteria to enable vertical and horizontal fill though.

I threw the box in the trash long ago, but occasionally somebody gives me a new one to think inside of

Cheers Jonmo and Jason,

Worked perfectly

You're welcome.