how do I average only certain cells based on month?
 

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?

how do I average only certain cells based on month?
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"jcannon1" wrote in message
...
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?

how do I average only certain cells based on month?
 

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?

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"jcannon1" wrote in message
...
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?

how do I average only certain cells based on month?
 

Try this:

=AVERAGE(A1:INDEX(A1:L1,MONTH(NOW())))

--
Biff
Microsoft Excel MVP


"jcannon1" wrote in message
...
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?