QUARTILE.INC returning a value of zero for the 0 QUART

Question

i'm using the quartile.inc() function in a pivot table. The column I'm using is SUM of profit. It doesn't have any 0 values in there (they have been filtered out) yet it keeps returning a 0 value.. Here's a screenshot of the function in question

I've made sure the data type is numeric across all tables, i've filtered out the $0.00 values. I know for a fact the correct value it should return in 15.08. The customer_ids have only blanks and one other id filtered out. Everything else works great except this.

Answer 1

You can try this:

=quartile.inc(filter($D$2:$D$3494,map($D$2:$D$3494,lambda(x,subtotal(103,x)))),0)

As pointed out Google Sheets lacks the AGGREGATE function Excel has which is able to perform aggregations like quartiles ignoring filtered rows; only the more basic SUBTOTAL function is available which retains the ability to ignore filtered rows but lacks more complex aggregations.

However, by using SUBTOTAL(103 (COUNTA ignoring filtered rows) within a MAP over a filtered vertical range you can obtain the filter status of each row as an array; this can be used as the condition argument to the FILTER function to obtain the filtered source cells as an array, which can then just be passed into QUARTILE.INC.