Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula help. Different result dependent on answer | Excel Worksheet Functions | |||
UserForm to select current month or earlier | Excel Discussion (Misc queries) | |||
is there a formula to generate a calendar month date rather than . | Excel Worksheet Functions | |||
looking for a formula | Excel Worksheet Functions |