Totalling/averaging non-consecutive rows

P

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.
 
B

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)
 
P

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
Click to expand...

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)?
 
B

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)
 
P

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.
Click to expand...

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

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