# Formula help needed

#### slouis

##### New Member
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"

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### FDibbins

##### Well-known Member
Try this...
=AVERAGEIFS(G:G,G:G,">"&MIN(G:G),G:G,"<"&MAX(G:G))

#### slouis

##### New Member
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
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

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

##### MrExcel MVP
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

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

##### Well-known Member
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

##### New Member
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
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)))``

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,440
Messages
5,837,256
Members
430,489
Latest member
ConsultPC

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

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