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))}
|