Adding Date Range to Averageif statement

L

lwilliams

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.
 
P

Pete_UK

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
 
M

Max

need to add an additional expression to
calculate by month and by quarter in addition to the status
Click to expand...

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
---
 
Top