Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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))} |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date range in an if then statement | Excel Discussion (Misc queries) | |||
Adding in a Date Range | Excel Worksheet Functions | |||
adding range of numbers to if statement with three criteria | Excel Worksheet Functions | |||
How do I get an IF statement to pull a date range?? | Excel Worksheet Functions | |||
if statement with date range | Excel Worksheet Functions |