Averageif ignore "########" value

[Protectortc]

I am using Excel 2010 on Windows XP.

Here is my Equation:

=AVERAGEIF('Call Entry Sheet DAYS'!E:E,'Average Response Time'!G7,'Call Entry Sheet DAYS'!K:K)

I need it to ignore the "########" errors in the K Column. There is no way I can fix the error without a lot of effort.

I have tried to add the ,"<>########" but it says that there are too many arguments. Is there another way I can do this, or someway I can make this work?

If you need more info, please ask. Thank you!

 

[konew1]

Doesn't ####### just mean the column is not wide enough to display the result? Widen the column, see what appears. It is probably an error that can then be corrected?
You could use IFERROR formula, but I think you need try the column width first

 

[Protectortc]

You just reminded me. I believe the error "########" is due to a negative result.

Column K is a Sum of Columns A and C. Not every row needs Column C, therefore creating the ######## error. I need the equation to skip that error. But anything I try to add to the equation and I get the following error:

"You've entered too many arguments for this function.

To get help with this function, click OK to close this message. Then click the Insert Function button located to the left of the equal sign in your formula."

 

[konew1]

The first part of my first posting did not appear. The ###### is because the cell result is too long to display on the column. I suggested you widen the column to see what the cell result really is, because #### is a display issue, not an error.
Once you widen the column you will see what is happening.

If it is an error that is acceptable to you then use =IFERROR(yourformula,"") to stop the error

 

[Andrew Poulsom]

If you want to ignore negative times try:

=AVERAGEIFS('Call Entry Sheet DAYS'!K:K,'Call Entry Sheet DAYS'!E:E,'Average Response Time'!G7,'Call Entry Sheet DAYS'!K:K,">0")

 

[Robert Mika]

You just reminded me. I believe the error "########" is due to a negative result.

Column K is a Sum of Columns A and C. Not every row needs Column C, therefore creating the ######## error. I need the equation to skip that error. But anything I try to add to the equation and I get the following error:

"You've entered too many arguments for this function.

To get help with this function, click OK to close this message. Then click the Insert Function button located to the left of the equal sign in your formula."

What do you see in Formula Bar(the one next to Fx sign)?

 

[Protectortc]

Thank you very much Andrew, that worked beautifully. You saved me hours of trying to figure that out my self!

Thank you Konew1 and Robert for your posts.