Average of every other cell

  • Thread starter Thread starter That's Confidential
  • Start date Start date
T

That's Confidential

Someone recently gave me an answer to this question, however it doesn't seem
to work so I will try again:-

In Column A, I have in ascending order "Attainment" and "Effort," so A3 =
Attainment, A4 = Effort, A5 = Attainment, A6 = Effort and so on, right down
until Cell A66 which is my last "Effort" cell. Now, what I want is for, in
another cell, to work out the average for all the scores in the Attainment
cell, so B3,B5,B7,B9 right down to B65, and likewise with the "Effort"
cells, B4,B6,B8 right down to B66.

Can anyone tell me what formula I would type in to work out the average of
the vale in every other cell?

Thanks in advance
 
Hi
try
=SUMPRODUCT(--(MOD(ROW(B3:B66),2)=1),B3:B66)/SUMPRODUCT(--(MOD(ROW(B3:B
66),2)=1))

or
=SUMIF(A3:A66,"Attainment",B3:B66)/COUNTIF(A3:A66,"Attainment")

or as array formula (entered with CTRL+SHIFT+ENTER)
=AVARAGE(IF(A3:A66="Attainment",B3:B66))
 
What if there were no labels in Column A? Is it possible to sum or average
every other cell based on Column B alone?
 
Sorry, your first formula meets that criteria.


RK said:
What if there were no labels in Column A? Is it possible to sum or average
every other cell based on Column B alone?
Click to expand...
 
Exploiting the regularity in the layout of your data...

Avg for Attainment:

=AVERAGE(IF((MOD(ROW($B$3:$B$66)-CELL("Row",$B$3)+0,2)=0),$B$3:$B$66))

Avg for Effort:

=AVERAGE(IF((MOD(ROW($B$3:$B$66)-CELL("Row",$B$3)+1,2)=0),$B$3:$B$66))

Both formulas must be confirmed with control+shift+enter instead of just
with enter.
 
Back
Top