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"

 

[Anand Sharma]

Try this

=SUMIFS(J1:J8,J1:J8,">"&MIN(J1:J8),J1:J8,"<"&MAX(J1:J8))/COUNTIFS(J1:J8,">"&MIN(J1:J8),J1:J8,"<"&MAX(J1:J8))

 

[Tetra201]

@Anand Sharma:

I am just curious -- how does your formula handle data filtering (mentioned in Posts #1, 3, 5, and 9)?

 

[slouis]

nope, this one is not working either. Here are the test results below. The answer should be 6.67. The 9 and both 2s should be removed, leaving only 5,7,8. This totals 20. There are three digits left. The division = 6.67

		Location	Duty	COST
		Away	sleep	2
		Away	sleep	9
		Away	sleep	8
		Away	sleep	7
		Away	sleep	5
		Away	sleep	2
RESULT =	5.50

<colgroup><col width="64" style="width: 48pt;"> <col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;"> <col width="64" style="width: 48pt;" span="3"> <tbody>
</tbody>

 

[slouis]

Sorry, here is the result. It still doesn't work but I wanted to show the correct calc (I had forgotten to insert as array)

		Location	Duty	COST
		Away	sleep	2
		Away	sleep	9
		Away	sleep	8
		Away	sleep	7
		Away	sleep	5
		Away	sleep	2
RESULT =	9.00

<colgroup><col width="64" style="width: 48pt;"> <col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;"> <col width="64" style="width: 48pt;" span="3"> <tbody>
</tbody>

 

[Tetra201]

Would you be OK with using a service column?

 

[slouis]

can you elaborate on that?

 

[Tetra201]

can you elaborate on that?

Are you willing to use an auxiliary column with a simple uniform formula in each cell to facilitate calculations of the final result?

 

[slouis]

yes I am willing

 

[Marcelo Branco]

nope, this one is not working either. Here are the test results below. The answer should be 6.67. The 9 and both 2s should be removed, leaving only 5,7,8. This totals 20. There are three digits left. The division = 6.67

		Location	Duty	COST
		Away	sleep	2
		Away	sleep	9
		Away	sleep	8
		Away	sleep	7
		Away	sleep	5
		Away	sleep	2
RESULT =	5.50

<tbody>
</tbody>

Maybe this...

	A​	B​	C​
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​	Formula​
21​	6,666666667​

<tbody>
</tbody>

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

Hope this helps

M.

 

[Tetra201]

yes I am willing

OK. I set column Z as the service column, but you can move it anywhere you like.

Put the following formula in cell Z2 and copy it down to beyond the last row of your data:

=--(AGGREGATE(3,5,$G2)>0)

For the final result, try the following formula (it is a regular formula, NOT an array one):

=(SUBTOTAL(109,$G$2:$G$20000)-SUMPRODUCT(($G$2:$G$20000=SUBTOTAL(104,$G$2:$G$20000))*($Z$2:$Z$20000))*SUBTOTAL(104,$G$2:$G$20000)-SUMPRODUCT(($G$2:$G$20000=SUBTOTAL(105,$G$2:$G$20000))*($Z$2:$Z$20000))*SUBTOTAL(105,$G$2:$G$20000))/(SUBTOTAL(102,$G$2:$G$20000)-SUMPRODUCT(($G$2:$G$20000=SUBTOTAL(104,$G$2:$G$20000))*($Z$2:$Z$20000))-SUMPRODUCT(($G$2:$G$20000=SUBTOTAL(105,$G$2:$G$20000))*($Z$2:$Z$20000)))