Totalling/averaging non-consecutive rows

[Paul Hyett]

Is there a relatively easy way to do this without having to type each
cell reference individually?

The non-consecutive rows would be evenly spaced, if that helps.

 

[Bob Phillips]

=SUMPRODUCT(--(MOD(ROW(A2:A200)-ROW(A2),2)=0),A2:A200)

=AVERAGE(IF((MOD(ROW(A2:A200)-ROW(A2),2)=0)*(ISNUMBER(A2:A200)),A2:A200))

the latter is an array formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Paul Hyett]

=SUMPRODUCT(--(MOD(ROW(A2:A200)-ROW(A2),2)=0),A2:A200)

=AVERAGE(IF((MOD(ROW(A2:A200)-ROW(A2),2)=0)*(ISNUMBER(A2:A200)),A2:A200))

the latter is an array formula, so commit with Ctrl-Shift-Enter

Thanks - I'll have a play with this.

Would the '2' in '-ROW(A2),2)' be the number of rows between the cells I
wanted to use?

Also, is there any limit to the number of rows this can be used for
(other than the overall Excel 2003 limit)?

 

[Bob Phillips]

No, that is just the start row of the range. The number of rows is the ,2
after that, the value MODed against.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Paul Hyett]

No, that is just the start row of the range. The number of rows is the ,2
after that, the value MODed against.

Sorry, I should have specified the second '2' was the one I meant.

The formulae have done what I wanted, though - thank you.