Average with a #N/A

[DDH]

The formula will not work when the N/A is in the column.
I thought this should work but something is in error

Thank you very much for your help.


IF(ISERROR(AVERAGEIFS($F$8:$F$372,$A$8:$A$372,"<>#n/a")<8-1-2015",$A$8:$A$372,"<>#n/a")>6-30-2015")),"0",AVERAGEIFS($F$8:$F$372,$A$8:$A$372,"<>#n/a")<8-1-2015",$A$8:$A$372,"<>#n/a")>6-30-2015"))

 

[Joe4]

So, are the values in column A your dates?
Are they actually in Date or Text format?

 

[DDH]

Joe4 - Thank you for your reply.

Col A is in Date format and Col B is in Time format.
Col C, D, E, F are in General format.

 

[Joe4]

OK, if I am understanding everything correctly, I think this may do what you want:

=AVERAGEIFS(F8:F372,F8:F372,"<>#N/A",A8:A372,">" & DATE(2015,6,30),A8:A372,"<" & DATE(2015,8,1))

 

[DDH]

Joe4 - Thank you.
You have a much cleaner and less cluttered formula then mine.
The formula worked perfect when it had a entry in the month.
I pasted the formula to the next month and changed the dates
AVERAGEIFS(F8:F372,F8:F372,"<>#N/A",A8:A372,">" & DATE(2015,7,31),A8:A372,"<" & DATE(2015,9,1))
it returned a #DIV/0!

When I pasted a entry to 8-1-15 the error went away

 

[Joe4]

You will get that error if there are no entries meeting your Criteria.
You can get rid of that by surrounding the whole formula in an IFERROR statement, i.e.

=IFERROR([COLOR=#333333]AVERAGEIFS(F8:F372,F8:F372,"<>#N/A",A8:A372,">" & DATE(2015,7,31),A8:A372,"<" & DATE(2015,9,1)),"")
[/COLOR]

Check out this for a discussion on the ISERROR function: Excel IFERROR Function

Note: You must have Excel 2007 or newer in order to have the IFERROR function. Otherwise, you would have to use the IF(ISERROR(... functionality you started to try to use in your first post.

 

[DDH]

Joe4 - Thank you for your reply.

I have Release 12.0 (2007 Office Excel 2007)

In my original formula (that was in error) I had used IF(ISERROR, I was not aware of the difference.
Would it be to my benefit to update to the most current Excel version (what ever that is)?
If I was to update would it harm my current worksheets?

Thank you so very much for all of your help and knowledge and insight.
I always learn so very much more then my original question when I ask for help.

 

[Joe4]

Since you have Excel 2007, you should have the IFERROR functionality. So you should be able to use my last formula without any issues.

 

[DDH]

Joe4 - It worked perfect.

Mine lack sophistication and class.
Thank you again for your help.

Joe4 IFERROR(AVERAGEIFS(F8:F372,F8:F372,"<>#N/A",A8:A372,">" & DATE(2015,7,31),A8:A372,"<" & DATE(2015,9,1)),"0")

Mine IF(ISERROR(AVERAGEIFS($F$8:$F$372,$A$8:$A$372,"<4-1-2015",$A$8:$A$372,">2-28-2015")),"0",AVERAGEIFS($F$8:$F$372,$A$8:$A$372,"<4-1-2015",$A$8:$A$372,">2-28-2015"))