I am trying to average the range s4:s219 (omitting #DIV/0!) with a couple of criteria:
average s4:s219 if
c4:c219 <8/1/2009 and
d4:d219 =Y
Here is the current formula I've been trying to work with, with no success so far.
=IFERROR(AVERAGEIFS($S$4:$S$219,$D$4:$D$219,"=Y",$C$4:$C$219,"<8/1/2009"),0)
Last edited by markl41; 07-19-2011 at 11:41 AM.
It works for me.
Are you sure the format of the dates in column C is the same as in the formula: 8/1/2009
ie. is that August 1, 2009 or Jan 8, 2009?
You can replace that part with the DATE(year,month,day) function to make it easier possibly.
=IFERROR(AVERAGEIFS($S$4:$S$219,$D$4:$D$219,"=Y",$C$4:$C$219,"<"&DATE(2009,8,1)),0)
change the 8 and 1 around if you mean Jan 8, 2009
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
I mean Aug 1, 2009 and yes the date format is the same.
The formula you've provided returns the same result I've been getting. The formula will return the zero, or whatever number is entered in the last slot after the comma for the =iferror function.
Are you sure there is a Y in column D at corresponding rows... and also that those Y's don't have an additional space or two before or after the letter....?
If you take out the IFERROR part do you get an error or 0?
Every cell in column C has a value (a date), not every cell in column D has a Y. There are no spaces before or after the Y.
I want the cells to average in column S if column D has a Y and column C is <8/1/2009
Compare the attached file to yours...Do you see any important differences?
Can you post the workbook or sample?
Like I said the formula should work if the conditions you set are true....
Here is a sample.
Ron,
Designating the date as its own cell and referencing the cell location rather than typing in the date didn't work either. I understand why it works on yours and I figure it'd work for me, but unfortunately it doesn't.
It is because you have cells in column S with text (i.e. #DIV/0!)
You need to exclude those:
Try:
=IFERROR(AVERAGEIFS($S$4:$S$219,$S$4:$S$219,"<>#DIV/0!",$D$4:$D$219,"=Y",$C$4:$C$219,"<"&DATE(2009,8,1)),0)
That works!
Thank you very much NBVC and Ron.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks