Calculating Average only if the difference between the values is less than 10%

[MurdochQuill]

Hi,

As per title, I'm calculating the average of unique ID's in my list via:

=AVERAGEIF($A$2:$A$110, A2, $B$2:$B$110)

ID:	Value	Average
Example1	10​	10.75​
Example1	15​	10.75​
Example1	12​	10.75​
Example2	59​	54.66667​
Example2	60​	54.66667​
Example3	100​	100​
4Example	500​	505​
4Example	510​	505​
Example1	6​	10.75​
Example2	45​	54.66667​

What I would like to do, is output an exception ("INVALID AVERAGE") if the difference between the lowest value and highest value for each unique ID being used in the AVERAGEIF is greater than 10%.

Any help would be appreciated, cheers.

 

[Snakehips]

I believe you have MINIFS and MAXIFS with Office 365

So maybe like. IF(MAXIFFS(Bla Bla)>(MINIFS(Bla bla)*1.1),"INVALID AVERAGE",AVERAGEIF($A$2:$A$110, A2, $B$2:$B$110))