AVG_IF with YTD array - too many arguements

V

Vulcan

A1 (current month), B2:M2 (Jan - Dec), B3:M10 (data).

I want to change the month in A1 to get an YTD average. If I use an
{AVG(if,(if,(if,(if))))} array I get a "too many arguments error."
 
B

barry houdini

Try

=AVERAGE(OFFSET(B3:B10,0,0,,MATCH(A1,B2:M2,0)))

I'm assuming that A1 just shows the month as text e.g. "Sep
 
A

alexrs2k

Hi.
Try this formula:
=ROUND(SUMPRODUCT(--(MONTH(B1:M1)<=MONTH(A1)),(B2:M2)/MONTH(A1)),2)

The cells containing the series of months should be formated as Date and
should have valid Date. If you want to show the month in letters go to Cell-
Format -Custom and type mmm for the first 3 letters.
 
Top