average if

[Keypro]

I need help with averaging with conditions
Example if {'FN Project Tracking'!$M$17:$M$8546=$B22} and if{ 'FN Project Tracking'!$G$16:$G$8546="BAU" } and is more then 14 days old average # of days dates are in {'FN Project Tracking'!$L$16:$L$8546 }{ 'FN Project Tracking'!$O$16:$O$8546 }
any help is greatly appreciated

 

[borgem]

Would something like this work? =IF(AND('FN Project Tracking'!$M$17:$M$8546=$B22),('FN Project Tracking'!$G$16:$G$8546="BAU"),(RANGE>=today())),AVERAGE('FN Project Tracking'!$L$16:$L$8546)+AVERAGE('FN Project Tracking'!$O$16:$O$8546),"")

 

[Keypro]

that is giving me a formula error
By the way this is Excel 2003 sp3

 

[Keypro]

Here is more clarification on " is more then 14 days start to finish, start date is in FN Project Tracking'!$L$16:$L$8546 and finish date is in FN Project Tracking'!$O$16:$O$8546 if less than 14 days do not calculate in the totals "
any questions that i can answer that can help please ask

 

[borgem]

So just to clarify, you want the average # of days if there are less than 14 between start an finish?

 

[Keypro]

longer than 14 days
but include the 14 in the average

 

[borgem]

For argument's sake, let's say this formula is in FN Project Tracking'T16 then try:

=IF(AND('FN Project Tracking'!$M$17:$M$8546=$B22),('FN Project Tracking'!$G$16:$G$8546="BAU"),DATE(FN Project Tracking'!$O16)-DATE(FN Project Tracking'!$L16)>=14,DATE(FN Project Tracking'!$O16)-DATE(FN Project Tracking'!$L16),"")

You would then copy this formula from my theoretical T16 down to T8546, then you would in U16 enter this formula =AVERAGE(FN Project Tracking'!T16:T8546)

You could hide the "T" column so the individual days counts don't show, just the overall average

Does this help at all?

Another Idea is that Maybe you would have to break it down more into:

=IF(AND('FN Project Tracking'!$M17=$B22),('FN Project Tracking'!$G16="BAU"),DATE(FN Project Tracking'!$O16)-DATE(FN Project Tracking'!$L16)>=14,DATE(FN Project Tracking'!$O16)-DATE(FN Project Tracking'!$L16),"")

I don't know what your data looks like, but should it be M17 or M16 since all other ranges go from "X"16:"X"8546

 

[Keypro]

http://www.yousendit.com/download/VGlkM25ORkVQb0ozZUE9PQ

 

[borgem]

What cell range are you placing this function in? '#'s for Dave'!$H$22:$H$33 ? I can't seem to follow your references because your it looks like your functions references in columns AH and AI are broken. I was trying to trace where data comes from...in the "Average Days to Design BAU" area, you refence AL, which relies on AH and AI, which in turn have apparently broken formulas/functions in them....did they ever reference a sheet outside of the one you posted?