Quartile Function - Not hidden rows

[BNL]

I'm working on a spreadsheet that computes a number of statistical results, including quartile functions. I want to be able to filter the rows and use only the rows that are NOT hidden within the calculations.

I would typically use the SUBTOTAL function, but it does not include the quartile function in it's options.

Is there any way to figure quartile calculations on only non-hidden rows?

Thanks!

Barry

 

[stanleydgromjr]

Barry,

See Excel Help for SUBTOTAL with a function number to ignore/include hidden values.

Function_num (includes hidden values) Function_num (ignores hidden values) Function.

Have a great day,
Stan

 

[barry houdini]

Is there any way to figure quartile calculations on only non-hidden rows?

Hello Barry (great name)

Assuming your range of cells is A2:A20 try this formula

=QUARTILE(IF(SUBTOTAL(2,OFFSET(A$2,ROW(A$2:A$20)-ROW(A$2),,1)),A$2:A$20),3)

confirmed with CTRL+SHIFT+ENTER

Change 3 at the end depending on quartile you wish to calculate

 

[BNL]

Ceases to Amaze Me

Thanks so much Barry - I like the last name. I tried your solution and naturally, it worked fantastically.

I didn't think about it in my initial post, but I have the same issue with the COUNTIF function. What would the formulae be for using this function in the same senerio?

I want to count say just the 1's in a column and only in those rows not hidden due to the filter.

Thanks so much!

Barry

 

[Domenic]

Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A20,ROW(A2:A20)-ROW(A2),0,1)),--(A2:A20=1))

Hope this helps!

 

[BNL]

Thanks so much - it works great.

Thank God for MrExcel Message Board.

Barry

 

[Sara Genius]

=QUARTILE(IF(SUBTOTAL(2,OFFSET(J$5,ROW(E$5:E$85)-ROW(J$5),,1)),J$5:J$85),1)

Hello all, this works brilliantly if you have one column of data to filter, but what if i have three?

My filter data is in columns E, F and G - for the life of me i can't get it to work with anything but E.

Any help would be much appreciated.

 

[Sara Genius]

Hello Barry,

This works brilliantly if you have one column of data to filter, but what if I have three?

=QUARTILE(IF(SUBTOTAL(2,OFFSET(J$5,ROW(E$5:E$85)-ROW(J$5),,1)),J$5:J$85),1)

My filter data is in columns E, F and G - for the life of me I can't get it to work with anything but E.

Any help would be much appreciated.

Sara.

Hello Barry (great name)

Assuming your range of cells is A2:A20 try this formula

=QUARTILE(IF(SUBTOTAL(2,OFFSET(A$2,ROW(A$2:A$20)-ROW(A$2),,1)),A$2:A$20),3)

confirmed with CTRL+SHIFT+ENTER

Change 3 at the end depending on quartile you wish to calculate

 

[XOR LX]

@Sara Genius

Firstly, are you aware that you're replying to a thread which is nearly 8 years old?

Secondly, what precisely are you wishing to do? The given solution will operate on whatever rows are left visible as a result of filtering, by they way, not on those as the result of hiding rows (which is technically different), as the title implies. For that, you would use an initial parameter of 102, not 2.

Regards

 

[Sara Genius]

I didn't realise it was that old! I'm glad it was still there nonetheless.

I seem to be getting the right results once i put in 102. I am sure i tried this yesterday, but anyhow, it is working now.

Thank you for taking trouble to reply to me. I'm working on other excel things now so may contact you again!

Best wishes,
Sara.

@Sara Genius

Firstly, are you aware that you're replying to a thread which is nearly 8 years old?

Secondly, what precisely are you wishing to do? The given solution will operate on whatever rows are left visible as a result of filtering, by they way, not on those as the result of hiding rows (which is technically different), as the title implies. For that, you would use an initial parameter of 102, not 2.

Regards