Averages - HELP!!!

[rduncan]

I have a sheet that contains data that repeats. For example, e10 to e27 contains a mail quantity, revenue, credit payments, etc. These same 18 sets of data are duplicated down the sheet for different groups. I need to find the average of the following: e26,e45,e64,e83,e102,e121,e140,e159. if it helps, the cells that i need to get the average of is every 19th cell. how the heck can i get this average???

 

[xld]

Try this array formula

=AVERAGE(IF((MOD(ROW(E26:E159),19)=7)*(E26:E159<>""),E26:E159))

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=AVERAGE(IF(MOD(ROW(E26:E159)-ROW(E26),19)=0,E26:E159))

To allow empty cells...

=AVERAGE(IF(MOD(ROW(E26:E159)-ROW(E26),19)=0,IF(E26:E159<>"",E26:E159)))

...which also needs to be confirmed with CONTROL+SHIFT+ENTER.

 

[SamTYler]

Pick an empty cell...type into it =AVERAGE(
Hold down the ctrl key and click on each of those 8 cells then hit the enter key.

 

[rduncan]

i forgot to mention that i don't want it to average in any of those cells if they contain 0. does that change the formulas put forth so far, any?

 

[Domenic]

i forgot to mention that i don't want it to average in any of those cells if they contain 0. does that change the formulas put forth so far, any?

Try...

=AVERAGE(IF(MOD(ROW(E26:E159)-ROW(E26),19)=0,IF(E26:E159>0,E26:E159)))

...confirmed with CONTROL+SHIFT+ENTER.

 

[SamTYler]

yeah. Don't click on the ones with a zero LOL

 

[rduncan]

Try...

=AVERAGE(IF(MOD(ROW(E26:E159)-ROW(E26),19)=0,IF(E26:E159>0,E26:E159)))

...confirmed with CONTROL+SHIFT+ENTER.

this was exactly what i needed! thanks!!