ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Adding Date Range to Averageif statement (https://www.excelbanter.com/excel-discussion-misc-queries/204924-adding-date-range-averageif-statement.html)

lwilliams

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

Pete_UK

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



Max

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