"duane_mi" wrote:
Maybe i didnt explain it correctly

You can say that again! Your description below bears no resemblance
whatsoever to your original formula.


"duane_mi" wrote:
H422:H427 are lists of date/times i want to average any
date greater then 1/6/2012 (L422). If that condition
passes I then average the dates in H422-H427. The problem
i have is that some of the dates are negative and my
formula does not work with neative dates so i need to
be able to ignore them and only average those dates with
postive results.

I don't know what you mean by "dates are negative". Excel does not support
negative date/times, unless you set the 1904 date system (not recommended).

Also, I find it odd to average date/times per se. Do you realize that the
average of 7/6/2012 and 7/7/2012 is 7/6/2012 12:00 (noon)? Is that really
what you mean?

(Note: I write dates in the form mm/dd/yyyy.)

I wonder if you really want to average the difference between H422:H427 and
L422, but only when H422:H427 is later than L422 (positive difference).

(Or perhaps the "date"/time is really a number of days and time.)

A concrete example would go a long way to demonstrating exactly what you
want. Provide the actual values that might be in H422:H427 and L422, with
some date/times before L422. And show us what the result of the average
should be.

Anyway, to implement exactly what you describe above, be it right or wrong,
you can array-enter the following formula (press ctrl+shift+Enter):

=AVERAGE(IF(H422:H427L422,H422:H427))

If you have Excel 2007 or later, you can write (normal-entered; just press
Enter):

=AVERAGEIF(H422:H427,H422:H427,""&L422)

But your previous formula actually compared D422:D427L422, and you averaged
H422:H427. So perhaps you really want (array-entered; press
ctrl+shift+Enter):

=AVERAGE(IF(D422:D427L422,H422:H427))

I don't know why you were multiplying that by H422:H427 previously. But if
that is what you want to do conditionally, you would write (array-entered;
press ctrl+shift+Enter):

=AVERAGE(IF(D422:D427L422,H422:H427^2))

Hmm, but I already mentioned that, and you claim it does not work. Did you
remember to array-enter the formula?