AVERAGEIF EXLUDING BOTH ZEROS AND OTHER CELL DATA (OUTLIERS)

[Benjamin Repsold]

Good day

Hope this mail finds you well.

I'm struggling a bit with my biostatistical data.

First =AVERAGEIF(B6:B36,"<>0")
But now, in each situation there are cells (e.g., B34, B18) that must be excluded (outliers).

How do I go about it without setting ranges and just click on the outliers ?

My sincere gratitude for your time and effort.

Dr. Benjamin P. Repsold

 

[AhoyNC]

It probably would have been better to start a new thread, instead of adding to such an old one.
See if the example below works for you. I'm calculating an outlier as (interquartile range * 1.5 )+ quartile 3 for upper limit and (interquartile range * 1.5 ) - quartile 1 for the lower in this example.
The formulas in D6 and E6 are array formulas that must be entered with CTRL-SHIFT_ENTER.

Book1
	A	B	C	D	E	F	G	H
3
4							Outlier	Outlier
5		Data		Quart. 1	Quart 3	InterQuart	Lower	Upper
6		48		48	62	14	27	83
7		44
8		62		Average	54.77778
9		64
10		49
11		61
12		0
13		56
14		56
15		62
16		64
17		38
18		42
19		65
20		46
21		0
22		65
23		58
24		49
25		100
26		47
27		60
28		60
29		54
30		45
31		59
32		39
33		98
34		65
35		59
36		62
Sheet1

Cell Formulas
Range	Formula
D6	D6	=QUARTILE(IF(B6:B36<>0,B6:B36),1)
E6	E6	=QUARTILE(IF(B6:B36<>0,B6:B36),3)
F6	F6	=E6-D6
G6	G6	=D6-(1.5*F6)
H6	H6	=E6+(1.5*F6)
E8	E8	=AVERAGEIFS($B$6:$B$36,$B$6:$B$36,"<>0",$B$6:$B$36,">"&$G$6,$B$6:$B$36,"<"&$H$6)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Benjamin Repsold]

Good day



Hope this mail finds you well.



I'm struggling a bit with my biostatistical data.



Please find attached file "Ermelo (Daily outliers)



First B37 =AVERAGEIF(B6:B36,"<>0")

But now, in each situation there are cells (e.g.,B35) that must be excluded (outliers).



Basically:



B37 equation must contain exclusions of 0 as well as exclusions of a few outliers



How do I go about it without setting ranges and just click on the outliers ?



CAN YOU PLEASE REWRITE EQUATION FOR B37, AND IF POSSIBLE E37 ?



There are =SUM(....)COUNT(.......)

My sincere gratitude for your time and effort, and please stay safe.



Dr. Benjamin P. Repsold

 

[Fluff]

@Benjamin Repsold
In future please do not make duplicate posts, especially after you have already had a response.
Thanks

 

[mikerickson]

You could do something like =AVERAGEIFS(B6:B36,B6:B36,">"&PERCENTILE.EXE(B6:B36,.1),,B6:B36,"<"&PERCENTILE.EXE(B6:B36,.9))