Average with conditions

I have the following formula
=AVERAGE(IF(D422427>L422,H422:H427,"FALSE")*H422 :H427)

That averages whats in H422:H427 if D422427 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(D422427>L422,H422:H427,"FALSE")*H422 :H427)

I cannot believe that formula works exactly as written. When one of
D422427 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 D422427 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(D422427>L422,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(D422427>L422,IF(H422:H427>0,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:H427>L422,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 D422427>L422, and you averaged
H422:H427. So perhaps you really want (array-entered; press
ctrl+shift+Enter):

=AVERAGE(IF(D422427>L422,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(D422427>L422,H422:H427^2))

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