Min/Max Average Formula Not showing single value

[pbundrant]

Here is my formula:

=IF(COUNT($H5:$AA5)>1,(SUM($H5:$AA5)-MIN($H5:$AA5))/(COUNT($H5:$AA5)-1),"")

The problem is that it removes a value when it is the only value in the row.

Can anyone suggest perhaps a different formula?

My goal is to find the "pessimistic avg" and the "optimistic avg". Removing the top and bottom values in the range.

 

[SkipVought]

hi,

Well what DO you want to return when there's only one value. Your ELSE value is currently an EMPTY STRING.

 

[zelgar]

Your logic was if there is more than 1 value in cells H5 to AA5 to do the calculation. Also, looking at your formula, since you are dividing by the COUNT($H5:$AA5)-1 if you changed the initial part of your IF statement to be >0, you'd get a DIV/0 error if you only had 1 value in cells H5 to AA5.

 

[pbundrant]

So is there a way to direct the ELSE value to the only value in the range?

 

[pbundrant]

I was given this formula in another forum and works beautifully!

=IF(COUNT($H12:$AA12)>1,(SUM($H12:$AA12)-MIN($H12:$AA12))/(COUNT($H12:$AA12)-1),IF(COUNT($H12:$AA12)=1,SUM($H12:$AA12),""))

 

[Deniall]

It works beautifully because it addresses the problem that Skip pointed out to you.