Quartiles of filtered data not including zeros

P

Philmason

New Member
Joined
Nov 5, 2018
Messages
1
Hi there

I have read threads that show me how to find non zero, filtered averages. I want to do the same thing but for 1st, 2nd and 3rd quartiles. Can anyone help please?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
The following array formula will return the quartile for a range of cells, excluding cells that contain 0 values, based on the filtered data...

Code: 
=QUARTILE.INC(IF(SUBTOTAL([COLOR=#ff0000]3[/COLOR],OFFSET(B2:B10,ROW(B2:B10)-ROW(B2),0,1)),IF(B2:B10<>0,B2:B10)),1)

Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the range accordingly. Also, to take into consideration rows that have been manually hidden, in addition to being filtered, replace the 3 in red with 103.

Hope this helps!
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Skrej
  • Solved
filtering out zero values from results
Replies
2
Views
396
Skrej
Skrej
R
  • Solved
Average of last three non-zero, but one i a column
Replies
7
Views
219
Fluff
Fluff
G
  • Solved
Average first n non-zero results in a row
Replies
2
Views
269
arthurbr
arthurbr
S
  • Question Question
How to get average of percentages in a column with filtered cells and excluding zero
Replies
2
Views
941
Fluff
Fluff
B
  • Question Question
Help needed with Finding which quartile does an employee lie in.
2
Replies
13
Views
1K
kevin9999
K

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

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