Formula help needed

[slouis]

Column G has entries below in its row(s):
3<o></o>
0<o></o>
0<o></o>
6<o></o>
9<o></o>
0<o></o>
8<o></o>
6<o></o>
<o> </o>
Thereare 8 entries. Lowest value is 0. Highest value is 9. Takeout all the lowest entries with “0” (B,C,F) and all the highest values (in this case it's 9 but there's only one of them). Highest here is “9” (E) and that leaves3, 6, 8, & 6. So 4 entries left, totaling 23. Avg = 5.75.

I'm looking for a formula that can handle this sequence. # of entries will vary. # of duplicate MIN & MAX will vary. Entries will be dependent on some manual filtering... so row designations will always be different but Column will always be "G"

 

[Snakehips]

Excuse my late involvement but if you have Excel 2010 or later then I think the AGGREGATE function is the way to go with this.
Still as far as I see it requiring a 'Helper' column, which you may hide.

Excel 2012
	A	B	C	D	E	F	G
1	Helper				Location	Duty	COST
2	3				Away	Work	3
4	#DIV/0!				Away	Work	0
5	#DIV/0!				Away	sleep	0
6	#DIV/0!				Away	Work	9
9	6				Away	Work	6
10	#DIV/0!				Away	Work	9
13	8				Away	Work	8
15	6				Away	Work	6
31			Result	5.75
Sheet1

Cell Formulas
Range	Formula
A2	=IF(OR(G2=AGGREGATE(5,7,G$2:G$30),G2=AGGREGATE(4,7,G$2:G$30)),G2/0,G2)
D31	=AGGREGATE(1,7,A2:A30)

Hope that helps.

 

[Marcelo Branco]

Or to facilitate the understanding and maintenance of the formula

	A​	B​	C​	D​	E​
1​	Location​	Duty​	COST​
3​	Away​	sleep​	2​
5​	Away​	sleep​	9​
7​	Away​	sleep​	8​
8​	Away​	sleep​	7​
12​	Away​	sleep​	5​
14​	Away​	sleep​	2​
18​
19​
20​	Max​	Min​	SUM​	Count​	Result​
21​	9​	2​	20​	3​	6,666667​

[/tr]

[/tr]

[/tr]

[/tr]
[/tr]
[/tr]

[/tr]

[/tr]
[/tr]
[/tr]

Formula in A21
=SUBTOTAL(4,C2:C17)

Formula in B21
=SUBTOTAL(5,C2:C17)

Formula in C21
=SUMPRODUCT(--SUBTOTAL(3,OFFSET(A2:A17,ROW(A2:A17)-ROW(A2),0,1)),--(C2:C17>B21),--(A21>C2:C17),C2:C17)

Formula in D21
SUMPRODUCT(--SUBTOTAL(3,OFFSET(A2:A17,ROW(A2:A17)-ROW(A2),0,1)),--(C2:C17>B21),--(A21>C2:C17))

Formula in E21 (Result)
=C21/D21

M.

 

[Marcelo Branco]

Different criteria

	A​	B​	C​
1​	Location​	Duty​	COST​
2​	Away​	Work​	3​
4​	Away​	Work​	0​
6​	Away​	Work​	0​
9​	Away​	Work​	6​
10​	Away​	Work​	9​
11​	Away​	Work​	0​
13​	Away​	Work​	8​
15​	Away​	Work​	6​
18​
19​
20​	Formula​
21​	5,75​

[/tr]

[/tr]

[/tr]
[/tr]

[/tr]

[/tr]

[/tr]
[/tr]

M.

 

[admiral100]

Hi ,

=(SUBTOTAL(109,G2:G17)-(SUBTOTAL(104,G2:G17)*(SUMPRODUCT(SUBTOTAL(3,OFFSET(G2:G17,ROW(G2:G17)-MIN(ROW(G2:G17)),,1)),ISNUMBER(SEARCH(MAX(G2:G17),G2:G17))+0))))/((SUBTOTAL(103,G2:G17))-(SUMPRODUCT(SUBTOTAL(3,OFFSET(G2:G17,ROW(G2:G17)-MIN(ROW(G2:G17)),,1)),ISNUMBER(SEARCH(0,G2:G17))+0))-(SUMPRODUCT(SUBTOTAL(3,OFFSET(G2:G17,ROW(G2:G17)-MIN(ROW(G2:G17)),,1)),ISNUMBER(SEARCH(MAX(G2:G17),G2:G17))+0)))