how to adjust formula dependent on current month
 

Would like to create a formula that will adjust dependent on the current
month. We are trying to get an average through the current month and do not
want to have to change the formula each month to include the latest month.

Thanks. - Ellen

You could use an the offset command. This command allows you to offset
(shift) the references in your formula. You will need a cell to tell Offset
how far over to go

Another idea is that if the numbers are blank until you have data, I think
excel will ignore them for average purposes

"Ellen" wrote:

Would like to create a formula that will adjust dependent on the current
month. We are trying to get an average through the current month and do not
want to have to change the formula each month to include the latest month.

Thanks. - Ellen

Say you had data in A2:B99 (headers in row 1). Dates in column A and quantities
in column B.

You could use this to average just the current month:

=average(if(text(a2:a99,"yyyymm")=text(today(),"yy yymm"),b2:b99))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

So you could use this kind of thing to get any dates through the current month:
=average(if(text(a2:a99,"yyyymm")<=text(today(),"y yyymm"),b2:b99))
(still an array formula)

If A2:A99 can contain empty cells...
=AVERAGE(IF((A2:A99<"")*(TEXT(A2:A99,"yyyymm")<=T EXT(TODAY(),"yyyymm")),
B2:B99))
(one cell, still an array formula)




Ellen wrote:

Would like to create a formula that will adjust dependent on the current
month. We are trying to get an average through the current month and do not
want to have to change the formula each month to include the latest month.

Thanks. - Ellen

--

Dave Peterson