If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Getting the average from a column using date constrants
Using Column A (Date) Formatted Monday, February 1, 2009.
I want to grab the Average of column Z (percentage) where the Month of the row is February. In a spreadsheet with 365rows the result set should be the average of 28days. I have read many posts, tried many examples, Below is the current formula I am working with =AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH( 10),MainSheet!Z3:Z100) I am getting a #DIV/0! I am only testing this on 100 rows as the year isn't complete, as such there is 365 days of data. Any help would be greatly appreciated... |
#2
|
|||
|
|||
Getting the average from a column using date constrants
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 "Trevor." wrote in message ... Using Column A (Date) Formatted Monday, February 1, 2009. I want to grab the Average of column Z (percentage) where the Month of the row is February. In a spreadsheet with 365rows the result set should be the average of 28days. I have read many posts, tried many examples, Below is the current formula I am working with =AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH( 10),MainSheet!Z3:Z100) I am getting a #DIV/0! I am only testing this on 100 rows as the year isn't complete, as such there is 365 days of data. Any help would be greatly appreciated... |
#3
|
|||
|
|||
Getting the average from a column using date constrants
First, did you intend to use MONTH(10) in your formula? That's going to get
you the month of 1/10/1900, or 1. Also, you have a different sized range for the Z column than for the A column You can use an SUMPRODUCT()s to get there - this one asks for month 2 =SUMPRODUCT(--(MONTH(A2:A100)=2),Z2:Z100)/SUMPRODUCT(--(MONTH(A2:A100)=2)) "Trevor." wrote: Using Column A (Date) Formatted Monday, February 1, 2009. I want to grab the Average of column Z (percentage) where the Month of the row is February. In a spreadsheet with 365rows the result set should be the average of 28days. I have read many posts, tried many examples, Below is the current formula I am working with =AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH( 10),MainSheet!Z3:Z100) I am getting a #DIV/0! I am only testing this on 100 rows as the year isn't complete, as such there is 365 days of data. Any help would be greatly appreciated... |
#4
|
|||
|
|||
Getting the average from a column using date constrants
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 |
#5
|
|||
|
|||
Getting the average from a column using date constrants
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 |
#6
|
|||
|
|||
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 . |
#7
|
|||
|
|||
Getting the average from a column using date constrants
This still gives me Div/0.
I added the Mainsheet! before the cell names as it is across worksheets. As for the variance in the row definitions, that was a typo when I rewrote my current formula, as was the Month value. Thanks, T. "Duke Carey" wrote: First, did you intend to use MONTH(10) in your formula? That's going to get you the month of 1/10/1900, or 1. Also, you have a different sized range for the Z column than for the A column You can use an SUMPRODUCT()s to get there - this one asks for month 2 =SUMPRODUCT(--(MONTH(A2:A100)=2),Z2:Z100)/SUMPRODUCT(--(MONTH(A2:A100)=2)) "Trevor." wrote: Using Column A (Date) Formatted Monday, February 1, 2009. I want to grab the Average of column Z (percentage) where the Month of the row is February. In a spreadsheet with 365rows the result set should be the average of 28days. I have read many posts, tried many examples, Below is the current formula I am working with =AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH( 10),MainSheet!Z3:Z100) I am getting a #DIV/0! I am only testing this on 100 rows as the year isn't complete, as such there is 365 days of data. Any help would be greatly appreciated... |
#8
|
|||
|
|||
Getting the average from a column using date constrants
Ok, then you need to add another condition to the formula that looks for the
specific category that you want averaged. Is there a column range that specifies the category? Let's assume column B is the category. AB1 = 2/1/2009 AC1 = 2/28/2009 AD1 = some category like Delivery =AVERAGEIFS(MainSheet!Z2:Z100,MainSheet!B2:B100,AD 1,MainSheet!A2:A100,"="&AB1,MainSheet!A2:A100,"= "&AC1) -- Biff Microsoft Excel MVP "Trevor." wrote in message ... 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 . |
#9
|
|||
|
|||
Getting the average from a column using date constrants
Guys,
Any help on this? Thanks, T. "Trevor." wrote: This still gives me Div/0. I added the Mainsheet! before the cell names as it is across worksheets. As for the variance in the row definitions, that was a typo when I rewrote my current formula, as was the Month value. Thanks, T. "Duke Carey" wrote: First, did you intend to use MONTH(10) in your formula? That's going to get you the month of 1/10/1900, or 1. Also, you have a different sized range for the Z column than for the A column You can use an SUMPRODUCT()s to get there - this one asks for month 2 =SUMPRODUCT(--(MONTH(A2:A100)=2),Z2:Z100)/SUMPRODUCT(--(MONTH(A2:A100)=2)) "Trevor." wrote: Using Column A (Date) Formatted Monday, February 1, 2009. I want to grab the Average of column Z (percentage) where the Month of the row is February. In a spreadsheet with 365rows the result set should be the average of 28days. I have read many posts, tried many examples, Below is the current formula I am working with =AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH( 10),MainSheet!Z3:Z100) I am getting a #DIV/0! I am only testing this on 100 rows as the year isn't complete, as such there is 365 days of data. Any help would be greatly appreciated... |
Thread Tools | |
Display Modes | |
|
|