Dates and arrays
 

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$1130)*($M$ 5:$M$1130)*($O$5:$O$113=0)*($O$5:$O$113= ),$O$5:$O$113))}

Dates and arrays
 

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.


--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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$1130)*($M$ 5:$M$1130)*($O$5:$O$113=0)*($O$5:$O$113=
),$O$5:$O$113))}

Dates and arrays
 

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$1130)*($M$ 5:$M$1130)*($O$5:$O$113=0)*(YEAR($O$5:$O$113)=AB 1),$O$5:$O$113))}

"T. Valko" wrote:

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.


--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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$1130)*($M$ 5:$M$1130)*($O$5:$O$113=0)*($O$5:$O$113=
),$O$5:$O$113))}

Dates and arrays
 

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

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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$1130)*($M$ 5:$M$1130)*($O$5:$O$113=0)*(YEAR($O$5:$O$113)=AB 1),$O$5:$O$113))}

"T. Valko" wrote:

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.


--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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$1130)*($M$ 5:$M$1130)*($O$5:$O$113=0)*($O$5:$O$113=
),$O$5:$O$113))}

Dates and arrays
 

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" wrote:

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

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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$1130)*($M$ 5:$M$1130)*($O$5:$O$113=0)*(YEAR($O$5:$O$113)=AB 1),$O$5:$O$113))}

"T. Valko" wrote:

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.


--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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$1130)*($M$ 5:$M$1130)*($O$5:$O$113=0)*($O$5:$O$113=
),$O$5:$O$113))}

Dates and arrays
 

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!

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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" wrote:

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

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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$1130)*($M$ 5:$M$1130)*($O$5:$O$113=0)*(YEAR($O$5:$O$113)=AB 1),$O$5:$O$113))}

"T. Valko" wrote:

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.


--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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$1130)*($M$ 5:$M$1130)*($O$5:$O$113=0)*($O$5:$O$113=
),$O$5:$O$113))}