Average with conditions

D

duane_mi

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

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

joeu2004

duane_mi said:
I have the following formula
=AVERAGE(IF(D422:D427>L422,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:D427>L422,H422:H427^2))

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


duane_mi said:
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:D427>L422,IF(H422:H427>0,H422:H427^2)))
 
D

duane_mi

Hi Joe,
both of the your suggestions dont seem to work. Maybe i didnt explain i
correctly H422:H427 are lists of date/times i want to average any dat
greater then 1/6/2012 (L422). If that condition passes I then averag
the dates in H422-H427. The problem i have is that some of the dates ar
negative and my formula does not work with neative dates so i need to b
able to ignore them and only average those dates with postive results
 
J

joeu2004

duane_mi said:
Maybe i didnt explain it correctly

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


duane_mi said:
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 D422:D427>L422, and you averaged
H422:H427. So perhaps you really want (array-entered; press
ctrl+shift+Enter):

=AVERAGE(IF(D422:D427>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(D422:D427>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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top