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"

 

[FDibbins]

Try this...
=AVERAGEIFS(G:G,G:G,">"&MIN(G:G),G:G,"<"&MAX(G:G))

 

[slouis]

Hmmm. Not working. I added the formula and it always calcs to 425.29. I filter some rows out & the cell box never changes... There are 11,800 rows of data. Filters choices are in Columns A thru F. It looks like it may be calcing all 11,800 rows and displaying 425.29. It never re-calcs when I filter for anything. Thoughts?

 

[FDibbins]

It worked for me...

	G​	H​
1​	3	5.75​
2​	0
3​	0
4​	6
5​	9
6​	0
7​	8
8​	6

H1=AVERAGEIFS(G:G,G:G,">"&MIN(G:G),G:G,"<"&MAX(G:G))

Perhaps you got the ranges wrong?

edit: Just read you post fully, I need to learn to do that more lol. What are you filtering on, because yes, that will average the entire ragnge

 

[slouis]

ADVERTISEMENT

Ahhh there lies the issue. I do not want to avg the entire range... only what's filtered. But if I remove the filters, it WILL avg the entire range. Below is an example of data. If I filtered for only "Away" Locations and "Work" duty, then you formula would work & display 5.75. But if I did not filter Location and filtered for only "Work" duty, then the formula does not work. So, the formula needs to be specific to the data that's filtered.

Hope this helps. Thanks,

Location	Duty	COST
Away	Work	3
Away	sleep	2
Away	Work	0
Away	sleep	9
Away	Work	0
Away	sleep	8
Away	sleep	7
Away	Work	6
Away	Work	9
Away	Work	0
Away	sleep	5
Away	Work	8
Away	sleep	2
Away	Work	6
Home	Work	2
Home	Work	5

<colgroup><col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="64" style="width: 48pt;" span="2"> <tbody>
</tbody>

 

[Tetra201]

Try the following formula (to be entered using Ctrl+Shift+Enter, not just Enter). Note: lightly tested.

<subtotal(104,$g$2:$g$1000)),$g$2:$g$1000)) sum(if(($g$2:$g$1000="">=SUM(IF(($G$2:$G$20000>SUBTOTAL(105,$G$2:$G$20000))*($G$2:$G$20000< SUBTOTAL(104,$G$2:$G$20000)),$G$2:$G$20000))/SUM(IF(($G$2:$G$20000>SUBTOTAL(105,$G$2:$G$20000))*($G$2:$G$20000< SUBTOTAL(104,$G$2:$G$20000)),1))<subtotal(104,$g$2:$g$1000)),1))[ html="" code]<=""></subtotal(104,$g$2:$g$1000)),1))[></subtotal(104,$g$2:$g$1000)),$g$2:$g$1000))>

 

[slouis]

ADVERTISEMENT

Hmmmm, not working either. Here is the result I get with your formula. The answer should be 5.75.

		Location	Duty	COST
		Away	Work	3
		Away	Work	0
		Away	Work	0
		Away	Work	6
		Away	Work	9
		Away	Work	0
		Away	Work	8
		Away	Work	6
RESULT =	4.909

<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>

 

[FDibbins]

I have built your filter into my formula - which is why I asked what you were filtering on

Based on the data supplied in post #5, I get an average of 5 (also tested manually)...

	G​	H​	I​	J​
1​	Location	Duty	COST	5.00​
2​	Away	Work	3​
3​	Away	sleep	2​
4​	Away	Work	0​
5​	Away	sleep	9​
6​	Away	Work	0​
7​	Away	sleep	8​
8​	Away	sleep	7​
9​	Away	Work	6​
10​	Away	Work	9​
11​	Away	Work	0​
12​	Away	sleep	5​
13​	Away	Work	8​
14​	Away	sleep	2​
15​	Away	Work	6​
16​	Home	Work	2​
17​	Home	Work	5​

J1=AVERAGEIFS(I:I,I:I,">"&MIN(I:I),I:I,"<"&MAX(I:I),H:H,"work")

 

[slouis]

OK, sorry, but the criteria in the formula needs to be independent of any/all other data in any/all other rows. Only Column "I" in your example can be included in the formula. I say this because the data I'm filtering is extensive. The filtering options are available on 4 different columns, varying between 12 to 53 options, depending on the column. I guess I should have mentioned that. I think there would be too many options to list in the criteria section of the formula. Thoughts?

 

[Tetra201]

Try this modified version (to be entered using Ctrl+Shift+Enter, not just Enter):

=(SUBTOTAL(109,$G$2:$G$20000)-SUM(IF($G$2:$G$20000=SUBTOTAL(104,$G$2:$G$20000),1))*SUBTOTAL(104,$G$2:$G$20000)-SUM(IF($G$2:$G$20000=SUBTOTAL(105,$G$2:$G$20000),1))*SUBTOTAL(105,$G$2:$G$20000))/(SUBTOTAL(102,$G$2:$G$20000)-SUM(IF(($G$2:$G$20000=SUBTOTAL(104,$G$2:$G$20000))*($G$2:$G$20000<>""),1))-SUM(IF(($G$2:$G$20000=SUBTOTAL(105,$G$2:$G$20000))*($G$2:$G$20000<>""),1)))