I have the following formula
=AVERAGE(IF(D422:D427L422,H422:H427,"FALSE")*H422 :H427)

That averages whats in H422:H427 if D422:D427 is greater than L422(which is a date). The problem i have if that some of the cells in column H have negative values and i cannot seem to ignore them.

"duane_mi" wrote:
I have the following formula
=AVERAGE(IF(D422:D427L422,H422:H427,"FALSE")*H422 :H427)

I cannot believe that formula works exactly as written. When one of
D422:D427 is not greater than L422, your IF expression returns the string
"FALSE", which you try to multiply. That will result in a #VALUE error.

Moreover, even if you correct that, your average will include zeros when
each of D422:D427 is not greater than L422. Normally, that is not what we
want. (You need to decide for yourself.)

So the formula should probably be written:

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

And if you use Excel 2007 or later, you can write:


"duane_mi" wrote:
The problem i have if that some of the cells in column H
have negative values and i cannot seem to ignore them.

Since you are squaring H422:H427, you do not really need to ignore negative
values. But if that is what you want, then:

=AVERAGE(IF(D422:D427L422,IF(H422:H4270,H422:H42 7^2)))

Hi Joe,
both of the your suggestions dont seem to work. Maybe i didnt explain it correctly 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.

"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?