Getting the average from a column using date constrants
NUTS!!!!
Excel 2007.
T.
"T. Valko" wrote:
Ok, what version of Excel are you using?
--
Biff
Microsoft Excel MVP
"Trevor." wrote in message
...
I guess I wasn't clear...
I have a excel document that contains 13 sheets, 1 Main Data sheet, then a
sheet for each month. In the main sheet I track input data for every day
of
the year, that shows among other things what percentage of the days orders
were pickup, walking or delivery. I am trying to create a formula that
will
populate a cell in each of the month's sheet, that shows what the average
daily percentage was for delivery. To achieve this I need to grab the
appropriate Monthly data from Column Z (Delivery %). To ensure I only grab
the values that relate to said month I need to constrain the subset of
column
Z against column A (which stores a long date) to ensure the appropriate Z
column records are for the month at hand.
While I think this is clear, I also accept that it might just sound like
junk...Any help is greatly appreciated.
Thanks,
Trevor.
"T. Valko" wrote:
Using Column A (Date) Formatted
Monday, February 1, 2009.
In a spreadsheet with 365rows the result set
should be the average of 28days.
So, you want the average for February 2009?
=AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH (10),MainSheet!Z3:Z100)
Are you using Excel 2007?
Try this (Excel 2007)...
Use cells to hold the date boundaries.
AB1 = 2/1/2009
AC1 = 2/28/2009
=AVERAGEIFS(MainSheet!Z2:Z100,MainSheet!A2:A100," ="&AB1,MainSheet!A2:A100,"="&AC1)
--
Biff
Microsoft Excel MVP
.
|