![]() |
Adding Date Range to Averageif statement
I am using the following statement to calculate an average value for a
specific status of a data set. I need to add an additional expression to calculate by month and by quarter in addition to the status. Even thought I am using Excell 2007, the calculation has to work on Excel 2003. {=AVERAGE(IF('MRR Log'!O8:O1769="Open",'MRR Log'!Q8:Q1769))} Thanks in advance for any help. -- Larry |
Adding Date Range to Averageif statement
You can do that like this:
=AVERAGE(IF(('MRR Log'!O8:O1769="Open")*(TEXT('MRR Log'!D8:D1769,"mmm- yy")="Jun-08"),'MRR Log'!Q8:Q1769)) assuming your dates are in column D. This will check for June 2008, but hopefully you can see how to change it for different months. Remember to use CSE to commit the formula after editting it. Hope this helps. Pete On Oct 3, 2:29*pm, lwilliams wrote: I am using the following statement to calculate an average value for a specific status of a data set. *I need to add an additional expression to calculate by month and by quarter in addition to the status. *Even thought I am using Excell 2007, the calculation has to work on Excel 2003. {=AVERAGE(IF('MRR Log'!O8:O1769="Open",'MRR Log'!Q8:Q1769))} Thanks in advance for any help. -- Larry |
Adding Date Range to Averageif statement
need to add an additional expression to
calculate by month and by quarter in addition to the status Assuming say, col B contains real dates for the additional criteria By Month/Yr, eg: Oct08 =AVERAGE(IF(('MRR Log'!O8:O1769="Open")*(TEXT('MRR Log'!B8:B1769,"mmmyy")="Oct08"),'MRR Log'!Q8:Q1769)) By Quarter, eg for 4th Q viz: Oct08, Nov08 & Dec08 =AVERAGE(IF(('MRR Log'!O8:O1769="Open")*(TEXT('MRR Log'!B8:B1769,"mmmyy")={"Oct08","Nov08","Dec08"}), 'MRR Log'!Q8:Q1769)) Above expressions to be array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula -- Max Singapore http://savefile.com/projects/236895 Downloads:18,500 Files:361 Subscribers:58 xdemechanik --- "lwilliams" wrote: I am using the following statement to calculate an average value for a specific status of a data set. I need to add an additional expression to calculate by month and by quarter in addition to the status. Even thought I am using Excell 2007, the calculation has to work on Excel 2003. {=AVERAGE(IF('MRR Log'!O8:O1769="Open",'MRR Log'!Q8:Q1769))} |
All times are GMT +1. The time now is 11:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com