Average based on the previous row

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

Hi All,

in row 2 I have dates jan 1 to jun 31
in row 3 I have some data.
now if in cell A60 i need the average of the data in row 3 only for
January....so if Jan starts in cell B2 and ends in cell AF2, I need the
average of data in B3 to AF3

please help.
Thanks
 
Try this:

=AVERAGE(IF(MONTH(B2:AF2)=1,B3:AF3))

This is an array formula, so once you have typed it in (or
subsequently edit it) you should commit it with CTRL-SHIFT-ENTER (CSE)
instead of just ENTER. If you do this correctly then Excel will wrap
curly braces { } around the formula when viewed in the formula bar -
you should not type these yourself.

Change the 1 to a 2 for February, etc.

Hope this helps.

Pete
 
Try this:

I'm assuming there are no empty cells in row 2.

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=AVERAGE(IF(MONTH(B2:FZ2)=1,B3:FZ3))

Biff
 
Hi Gary

One way

=SUMPRODUCT((MONTH(B2:AF2)=1)*(B2:AF2<>"")*B3:AF3)/
SUMPRODUCT((MONTH(B2:AF2)=1)*(B2:AF<>""))
 
Back
Top