Hi,
I have a spreadsheet (1) where there are weekend dates in each column of row 1 and values in the rows below each of those dates. I would like to write a formula in the 2nd tab that will show me the averages of these values for each month. For example, for all columns, in sheet (1), that have a weekend date in the month of January 2014, I would like to show, in sheet (2), the average of the values in row 2 for those columns. I need to do this for each month from September 2013 through January 2015. I am attaching my spreadsheet to, hopefully, make it clearer.



Thanks,
Jodie

Attached Files

Averages.xlsx (12.7 KB, 19 views)

Hi

does the attached help ? ( hope I didn't mix up with the relative/absolute ref )

Attached Files

Averages.xlsx (15.2 KB, 15 views)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thank you very much Pecoflyer. This is exactly what I needed and the absolutes are where they need to be.

Now, I just have to figure out how each of the functions work so I can use them again.

I think I know, but I am not sure what the "+1" does. I believe the "-1" is subtracting a month, but I don't understand what is being added with the "+1".

Thanks,
Jodie

EOMONTH returns the last day of a month.
Using -1 gives the last day of the month before, and adding 1 returns the first day of the month

So, EOMONTH(today(),-1) returns Sep 30th, and adding 1 gives Oct 1st

The result can be obtained other ways ( like DATE) but it would have made the formula longer, or using >eomonth(ref,-1) instead of >=.

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thank you Pecoflyer. This is very helpful.