Formula help needed

slouis

New Member
Joined
Dec 2, 2016
Messages
11
Column G has entries below in its row(s):
3<o:p></o:p>

0<o:p></o:p>
0<o:p></o:p>
6<o:p></o:p>
9<o:p></o:p>
0<o:p></o:p>
8<o:p></o:p>
6<o:p></o:p>
<o:p> </o:p>
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"
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
Try this...
=AVERAGEIFS(G:G,G:G,">"&MIN(G:G),G:G,"<"&MAX(G:G))
 

slouis

New Member
Joined
Dec 2, 2016
Messages
11
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

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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
 
Last edited:

slouis

New Member
Joined
Dec 2, 2016
Messages
11

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,

LocationDutyCOST
AwayWork3
Awaysleep2
AwayWork0
Awaysleep9
AwayWork0
Awaysleep8
Awaysleep7
AwayWork6
AwayWork9
AwayWork0
Awaysleep5
AwayWork8
Awaysleep2
AwayWork6
HomeWork2
HomeWork 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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
Try the following formula (to be entered using Ctrl+Shift+Enter, not just Enter). Note: lightly tested.
Code:
<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))>
 
Last edited:

slouis

New Member
Joined
Dec 2, 2016
Messages
11

ADVERTISEMENT

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

LocationDutyCOST
AwayWork3
AwayWork0
AwayWork0
AwayWork6
AwayWork9
AwayWork0
AwayWork8
AwayWork6
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

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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​
LocationDutyCOST
5.00​
2​
AwayWork
3​
3​
Awaysleep
2​
4​
AwayWork
0​
5​
Awaysleep
9​
6​
AwayWork
0​
7​
Awaysleep
8​
8​
Awaysleep
7​
9​
AwayWork
6​
10​
AwayWork
9​
11​
AwayWork
0​
12​
Awaysleep
5​
13​
AwayWork
8​
14​
Awaysleep
2​
15​
AwayWork
6​
16​
HomeWork
2​
17​
HomeWork
5​
J1=AVERAGEIFS(I:I,I:I,">"&MIN(I:I),I:I,"<"&MAX(I:I),H:H,"work")
 

slouis

New Member
Joined
Dec 2, 2016
Messages
11
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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
Try this modified version (to be entered using Ctrl+Shift+Enter, not just Enter):
Code:
=(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)))
 

Watch MrExcel Video

Forum statistics

Threads
1,130,051
Messages
5,639,773
Members
417,112
Latest member
PachRedoc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top