statistics - Why does this Excel formula work? -

in this answer following formula given.

{=percentile(if(column(indirect("1:"&max(data!n3:n15)))<=data!n3:n15,data!m3:m15,""),0.25)} 

it supposed calculate first quartile of data weights.

i've been trying understand formula says, can't.

first of all, curly brackets? (probably silly question.)

second, <= operator when it's given 2 data ranges operands?

thrid, how can possibly give right answer, if, regardless of condition in if-statement does, returned data value column? seems me formula is

• if weird condition satisfied, calculate first quartile of value column.
• if it's not, calculate first quartile of "".

this seems wrong...

also, can find complete manual of excel functions , operators? online file says nothing comparing 2 ranges.

personally wouldn't use quoted formula - doubt if author thinks , in circumstances give incorrect results. can see problem because part

=column(indirect("1:"&max(data!n3:n15))

returns same thing if max(data!n3:n15) 2 or 200. if max(data!n3:n15)=2 get

=column(indirect("1:2"))

and assume author's intent return array {1,2}.......but doesn't that. indirect("1:2") gives 1:2 interpreted whole of rows 1 , 2 =column(1:2) gives numbers of columns (which either 1 256 or 1 16384 depending on version of excel using or whether using compatability mode or not).

you can test using formula in cell

=count(column(indirect("1:"&max(data!n3:n15))))

confirmed ctrl+shift+enter

you'll either 256 or 16384 doesn't depend on values in column n @ all.

the formula may give correct result might not work correctly if values in data!n3:n15 > 256 (or 16384 depending on version).

this version of formula should intended in cases:

=percentile(if(transpose(row(indirect("1:"&max(data!n3:n15)))) <=data!n3:n15,data!m3:m15,""),0.25)

.....but explain how works lets @ cut down version 4 rows, i.e.

=percentile(if(transpose(row(indirect("1:"&max(data!n3:n6))))<=data!n3:n6,data!m3:m6,""),0.25)

and assume m3:m6 contains these values - 10, 75, 15, 23 , n3:n6 contains these values 1,2,3,4,

now max(data!n3:n6) = 4 indirect gives "1:4" , passed row function array {1;2;3;4} [which "column" of values] transpose converts {1,2,3,4} [which "row" of values] - reason conversion because when column compared row or vice versa compares every value in 1 array every value in other array needed here (giving 4x4 matrix of values result).

now when every value in {1,2,3,4} <= 4 (n6 value) 3 <= 3 (n5 value), 2 <= 2 (n4 value) etc. array passed percentile function correctly returns 1 value of 10, 2 values of 75, 3 values of 15 , 4 values of 23 (the other values "" blanks percentile ignores)

the result of example 15 , original formula gives 15.......but explained above original formula might incorrect results larger numbers - e.g. i'm testing compatability mode in excel 2007 , if change n4 4000 , n6 1000 expect result 75 (which result formula gives) .....but original formula gives 23.