Totalling/averaging non-consecutive rows

  • Thread starter Thread starter Paul Hyett
  • Start date Start date
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.
 
=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)
 
=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)?
 
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)
 
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.
 
Back
Top