Average out percentage ignoring a specific negative number

[ToxikRick]

Hello,

I have been getting help from your forum for quite a while, but I've always found the answer. This is the first time I needed to ask one.

I have a spread sheet where I am trying to show the average percentage over a few cells. I'm trying to make R10 average out R5:R9, but to ignore -80% (-79% or more could be a valid number).

The -80% is in there because R5:R9 are pulling other info I want and -80% is the default info. I need R10 to show a running total, which it will not do if it's including the default -80% in it's formula.

I hope that made sense. Help is appreciated!

 

[AlanY]

try

=AVERAGEIF(R5:R9,"<>-80%",R5:R9)

or

=AVERAGE(IF(R5:R9<>-80%,R5:R9))
confirm with shift-control-enter together

 

[ToxikRick]

try

=AVERAGEIF(R5:R9,"<>-80%",R5:R9)

or

=AVERAGE(IF(R5:R9<>-80%,R5:R9))
confirm with shift-control-enter together

The second one worked amazing! Thank you.

Can I add a follow up of:

How can I make the same cells ignore -30% or lower (-31%, -32%, etc.). I have a similar set of cells that I just noticed need to do this.

 

[AlanY]

try this, again confirm with shift-control-enter

=AVERAGE(IF(R5:R9<>-80%,IF(R5:R9>-30%,R5:R9)))