Printable View
the following formula work fine when cell H2010 is blank, but as soon as i put a date into the cell, the formula returns 0. cell H2009 also contains a date, and the formula works weather it has a date or is blank. the formula resides in cell H2011.
=IF(H2010<>"",AVERAGE(IF(AND(J:J>H2009,J:J<H2010),M:M)),AVERAGE(IF(J:J>H200 9,M:M)))
You have a gap in 2009 at the end of your formula. It says 200 9.
<-- edit: n00b
Hi Gavin,
That is a common posting problem.
Regards
MD
that is an error in the forum. there is no gap in my equation, and there is no space when i try to edit post.
Your J:J is evaluating as 0. That is why. What are you trying to do?
i am trying to average data, that falls between two dates.
J:J =Date associated with data
M:M =Data
H2009 =Date that the data must start after
H2010 =Data that the data must start before.
=IF(H2010<>"",
AVERAGE(IF((J2:J200>H2009)*(J2:J200<H2010),M2:M200)),
AVERAGE(IF(J2:J200>H2009,M2:M200)))
this is an array formula, so commit with Ctrl-Shift-Enter, and adjust those ranges to suit, you cannot use whole columns pre-Excel 2007
Here's a non fancy way to do it and leaves room for error checking. You'll need to change the > and < to >= and =< if you want the dates to be inclusive.
Quote:
Originally Posted by xld
Slick. I suck at array formulas and need to get better with them. Any idea why using the evaluate formula on this crashes excel?
edit: excel 2007 on a better computer (I'm on a P3) did not crash evaluating. So it's either the program or junk PC
thanks, i dont know why i didn't think of Multiplying for and.
Didn't crash on my 2003 desktop.Quote:
Originally Posted by grichey
is there an equation to do this were i only need to put the year in H2009 and H2010?
That's what xld's solution is. He set the range to 200. Just change it to 65000 if you want to do the whole column or 2011 if that's the end of your data set.Quote:
Originally Posted by figment
sorry if i was vague with that last post, but right now i have to put a full date it cells H2009 and H2010, i would like to only put the year part of the Date, so if i want to find the data between the years 2001 and 2003 then i would set:
H2009 = 2001
H2010 = 2003
in stead of the current set up where i have to put:
H2009 = 12-31-2000
H2010 = 1-1-2004
I havent tried this but this might work:
=IF(H2010<>"",
AVERAGE(IF((year(J2:J200)>year(H2009))*(year(J2:J200)<year(H2010)),M2:M200) ),
AVERAGE(IF(year(J2:J200)>year(H2009),M2:M200)))
i tried this and it didn't work.
well you can def just insert a column at J and do J1: =year(K1) and fill all the way down and modify accordingly
yay i might have to do that, i was hoping not to, for that would significantly increases the size of the file.
Try this slight variation of Gavin's formula
=IF(H2010<>"",
AVERAGE(IF((YEAR(J2:J200)>=YEAR(H2009))*(YEAR(J2:J20)<=YEAR(H2010)),M2:M200 )),
AVERAGE(IF(YEAR(J2:J200)>=YEAR(H2009),M2:M200)))
i am getting the error "a value used in the formula is of the wrong Data type", now i do have cells in J:J where we dont know the date of a file. these cells are filled with "N/A" to represent this missing data. could this be messing up the equation?
Yes as year operates off dates whether in 'date' format or numerical format.
if you use 0 instead of NA it will assume they are 1900 which I would guess is wayyyy outside your dataset
that appears to work. thanks guys.
Maybe
=IF(H2010<>"",
AVERAGE(IF(ISNUMBER(J2:J200),IF((YEAR(J2:J200)>=YEAR(H209))*(YEAR(J2:J200)< =YEAR(H10)),M2:M200))),
AVERAGE(IF(ISNUMBER(J2:J200),IF(YEAR(J2:J200)>=YEAR(H2009),M2:M200))))
and XLD takes it a step farther for the Win