If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Average with conditions
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Average with conditions

#1
July 2nd 12, 12:55 PM
 duane_mi Junior Member First recorded activity by ExcelBanter: Jun 2012 Posts: 5
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.
#2
July 2nd 12, 04:29 PM posted to microsoft.public.excel.misc
 joeu2004[_2_] external usenet poster Posts: 637
Average with conditions

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

#3
July 3rd 12, 09:10 AM
 duane_mi Junior Member First recorded activity by ExcelBanter: Jun 2012 Posts: 5

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.
#4
July 6th 12, 11:23 PM posted to microsoft.public.excel.misc
 joeu2004[_2_] external usenet poster Posts: 637
Average with conditions

"duane_mi" > wrote:
> Maybe i didnt explain it correctly

You can say that again! Your description below bears no resemblance

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

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Average with Conditions Curtis[_2_] Excel Worksheet Functions 1 May 13th 10 04:34 PM Average with Conditions Curtis[_2_] Excel Worksheet Functions 3 April 10th 10 03:01 AM AVERAGE with conditions mr_concrete Excel Worksheet Functions 3 February 7th 07 08:23 PM average with 2 conditions s Excel Programming 6 March 15th 06 01:51 PM average on 2 conditions Ted Metro Excel Worksheet Functions 6 January 7th 05 08:23 PM

All times are GMT +1. The time now is 02:45 AM.