| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating Average based on 3 filled cells from left in a row | Excel Discussion (Misc queries) | |||
| Average If - averaging cells based on value of another cell | Excel Worksheet Functions | |||
| Average Less a Month | Excel Discussion (Misc queries) | |||
| 12 Month Average | Excel Worksheet Functions | |||
| =AVERAGE(IF(INT(MONTH(C:C)+2/3)=4 | Excel Worksheet Functions | |||
![jcannon1[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
