PDA

View Full Version : Solved: problem with a conditional average



figment
06-24-2008, 07:59 AM
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>H2009,M:M)))

grichey
06-24-2008, 08:42 AM
You have a gap in 2009 at the end of your formula. It says 200 9.

<-- edit: n00b

mdmackillop
06-24-2008, 08:45 AM
Hi Gavin,
That is a common posting problem.
Regards
MD

figment
06-24-2008, 08:46 AM
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.

grichey
06-24-2008, 08:52 AM
Your J:J is evaluating as 0. That is why. What are you trying to do?

figment
06-24-2008, 08:55 AM
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.

Bob Phillips
06-24-2008, 09:05 AM
=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

grichey
06-24-2008, 09:11 AM
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.

grichey
06-24-2008, 09:18 AM
=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


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

figment
06-24-2008, 09:38 AM
thanks, i dont know why i didn't think of Multiplying for and.

Bob Phillips
06-24-2008, 09:49 AM
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

Didn't crash on my 2003 desktop.

figment
06-24-2008, 10:52 AM
is there an equation to do this were i only need to put the year in H2009 and H2010?

grichey
06-24-2008, 11:45 AM
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.

figment
06-24-2008, 12:05 PM
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

grichey
06-24-2008, 12:27 PM
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)))

figment
06-24-2008, 12:29 PM
i tried this and it didn't work.

grichey
06-24-2008, 12:32 PM
well you can def just insert a column at J and do J1: =year(K1) and fill all the way down and modify accordingly

figment
06-24-2008, 12:40 PM
yay i might have to do that, i was hoping not to, for that would significantly increases the size of the file.

Bob Phillips
06-24-2008, 12:41 PM
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)))

figment
06-24-2008, 12:56 PM
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?

grichey
06-24-2008, 01:50 PM
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

figment
06-24-2008, 02:07 PM
that appears to work. thanks guys.

Bob Phillips
06-24-2008, 02:10 PM
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))))

figment
06-24-2008, 02:25 PM
and XLD takes it a step farther for the Win