average(If) using date

  • Thread starter Thread starter Tim Sullivan
  • Start date Start date
T

Tim Sullivan

I tried using the format from a different post but it
doesn't seem to work

What I tried is this =AVERAGE(IF(AND(CE26:CE750>=DATE
(1,1,2003),CE26:CE750<=DATE(12,31,2003)),CD26:CD750,""))
using array shft/cntrl/entr

What I want to do is average the numbers in column CD rows
25 thru 750 IF the date in column CE row 26 thru 750 is
between 1/1/2003 and 12/31/2003.
So below I want to averge just the numbers in colum CD
where the corresponding date in column CE is in 2003 or
just the first 8 numbers in CD

Sample data
CD CE
17.55 6/6/2003
12.47 6/2/2003
47.76 5/9/2003
9.85 5/6/2003
21.35 4/25/2003
108.01 4/23/2003
132.58 4/14/2003
9.77 2/19/2003
53.12 12/23/2002
39.1 12/18/2002
17.84 12/9/2002
30.01 12/3/2002
20.19 11/13/2002
0 9/27/2002
 
Tim Sullivan said:
I tried using the format from a different post but it
doesn't seem to work

What I tried is this =AVERAGE(IF(AND(CE26:CE750>=DATE
(1,1,2003),CE26:CE750<=DATE(12,31,2003)),CD26:CD750,""))
using array shft/cntrl/entr

What I want to do is average the numbers in column CD rows
25 thru 750 IF the date in column CE row 26 thru 750 is
between 1/1/2003 and 12/31/2003.
So below I want to averge just the numbers in colum CD
where the corresponding date in column CE is in 2003 or
just the first 8 numbers in CD

Sample data
CD CE
17.55 6/6/2003
12.47 6/2/2003
47.76 5/9/2003
9.85 5/6/2003
21.35 4/25/2003
108.01 4/23/2003
132.58 4/14/2003
9.77 2/19/2003
53.12 12/23/2002
39.1 12/18/2002
17.84 12/9/2002
30.01 12/3/2002
20.19 11/13/2002
0 9/27/2002
Click to expand...

First correct syntax of DATE functions. It is DATE(year,month,day), not
DATE(month,day,year).
 
Tim,

You can use the *IF formulas to manually calculate the average

=SUMIF(CE26:CE750,">1/1/03",CD26:CD750)/COUNTIF(CE26:CE750,">1/1/03")

OR

=AVERAGE((IF(CE26:CE750>DATE(2003,1,1),(CD26:CD750))))
Array entered (control + shift + enter) (you'll see {} around the
formula if it enters properly.

Dan E
 
The solution you gave doesn't address the range of dates.
My error in the sample data cause there are cases where I
will need to average a column where the date range is in
the middle of a range of dates
 
Tim,

I apologize, ignore my last post, i didn't read your post well
enough. That first formula should be

=(SUMIF(E2:E15,">=1/1/03",D2:D15)-SUMIF(E2:E15,">31/12/03",D2:D15))/(COUNTIF(E2:E15,">=1/1/03") -COUNTIF(E2:E15,">31/12/03"))

And the second should be

=AVERAGE(IF((CE26:E750>=DATE(2003,1,1))*(CE26:E750<=DATE(2003,12,31))*(CD26:D750)<>0,(CD26:D750),""))
array entered (control + shift + enter)

The problem with your formula seemed to be with the AND
statement... for some reason it was only returning a single
FALSE (if any of the dates were <1/1/03 or >12/31/03)

Dan E
 
You can also you the Daverage function to get the average that you are
looking for.

=daverage(data range,column to average,Criteria)

The input range is the actual data including headings.

Ccolumn to average is the column that contains the values to average.

Criteria includes a header and the data to look for
 
Back
Top