Sum of data base on current date

[beemerboy]

I have a row of forecasted numbers that I want to average
based on the current month. In the example below. I want
to use the today function to determine if a date has
passed and then average numbers from the first month
(jan) to the current month (june) and exclude any future
months. How can I do this?

Is there a way to dynamically feed the end boundary of
the array function (=average(start:end) with the cell
reference of the last month? I was able to get the cell
reference but when I try to use it, I keep getting the
VALUE error because it is looking for a number (I think).

Jan feb mar apr may june jul aug sept oct
200 100 150 250 300 5000 300 400 5000 600

 

[Juan Sanchez]

Heres one way:

=AVERAGE(IF(MONTH(A1:J1)<=MONTH(TODAY()),A2:J2,""))

This is an array formula, so, CTRL+SHIFT+ENTER...

You need to have date values on your headers, one easy way
is to change Jan-Feb-Mar with the first day of each month
and then custom Format the cells as MMM, so you only get
the month...

Hope it helps... Cheers. Juan

 

[Domenic]

Hi,

If your dates are not true dates, as I suspect, assuming that you ensure
your dates are all three letter abbreviations matching the following
formula, and assuming that K1 contains the three letter abbreviation for
the current date, try,

=AVERAGE(A2:INDEX(A2:J2,VLOOKUP(K1,{"jan",1;"feb",2;"mar",3;"apr",4;"may"
,5;"jun",6;"jul",7;"aug",8;"sep",9;"oct",10},2,0)))

Of course, you can always change all of the abbreviations for your table
and formula to their full names, if you prefer.

If all of your dates are in fact true dates, try,

=AVERAGE(IF(MONTH(A1:J1)<=MONTH(K1),A2:J2))

entered using CTRL+SHIFT+ENTER.

Hope this helps!