Dates and arrays

[Guest]

In my array I have several conditions. I would like one of those conditions
to look at a date (in another column; 01-sep-2006) and select those in a
specific year (i.e.
2006.

{=AVERAGE(IF(($A$5:$A$113=Q7)*($K$5:$K$113>0)*($M$5:$M$113>0)*($O$5:$O$113>=0)*($O$5:$O$113= ),$O$5:$O$113))}

 

[T. Valko]

You would use something like this:

(YEAR(range)=2006)

I notice your formula is referencing the same range 3 times.

($O$5:$O$113>=0)
($O$5:$O$113= )
$O$5:$O$113)

So, you want to test this range for the year *and* you want this range to be
the values that are averaged. So, I can only assume you want the average
DATE. If that's the case then you can eliminate this test:

($O$5:$O$113>=0)

Since testing for the year renders that test superfluous.

 

[Guest]

Hi and thanks for the guidance. I am actually use the last condition to get
the average of numbers in another column. That is why I can't get rid of it.
I tried your suggestion two ways, using "2006" and referencing a cell with
2006 in it.
Came up with a #NUM! Here it is:

{=AVERAGE(IF(($A$5:$A$113=Q7)*($K$5:$K$113>0)*($M$5:$M$113>0)*($O$5:$O$113>=0)*(YEAR($O$5:$O$113)=AB1),$O$5:$O$113))}

 

[T. Valko]

What do you have in O5:O113? Your formula is attempting to average the
values in O5:O113.

 

[Guest]

The value is the difference between 2 dates (see item Conditional 4 below)
Conditiona1 1 is searching for a specific name.
2. is making sure there is a value for a date
3. is making sure there is a value for another date
4. is making sure the value is positive (data quality)
5. The intent is to pick out the values that occur in a given year, based on
#2.

Hope that helps.

 

[T. Valko]

4. is making sure the value is positive (data quality)

You're also testing the same range as #4 for the year. If there are negative
values in that range then you'll get the #NUM! error. I think you're testing
the wrong range for the year!