how do I average only certain cells based on month?

  • Thread starter Thread starter jcannon1
  • Start date Start date
J

jcannon1

I am creating a spreadsheet that tracks our monthly goals for sales. My
spreadsheet looks like so:

Jan Feb Mar ETC
5000 5000 5000

I also have a portion for a YTD Average - I want the YTD average column to
pull the average based on the current month - ie if its February I want it to
add Jan + Feb / 2.... if its March I want it to add Jan + Feb + Mar / 3....

Can anybody help me on this?
 
If the three-letter months are text, and the first one ("Jan") in in A1
=SUMPRODUCT(--(COLUMN(A1:L1)<=MONTH(TODAY())),A2:L2)

If the three-letter monts are actally dates like 1/Jan/2009, 1/Feb/2009
formatted with "mmm"
=SUMPRODUCT(--(MONTH(A1:L1)<=MONTH(TODAY())),A2:L2)
best wishes
 
This is getting closer... My data starts in column C1 ( columns A1 and B1
have descriptions of each budget). When I copy your formula into my worksheet
it returns a zero value which I know isn't correct.

I copied the actual data and formula into another worksheet and started at
column A1 and the formula worked.

Unfortunately I can't remove the data I have in columns A & B so is there
another way to state the formula?
 
Back
Top