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.


Comments

Post a Comment

Popular posts from this blog

Issues changing the value of an element in an array in matlab -

-
i'm trying create program sum n-1 , n-2 numbers create n , store value in array. currently, output (assuming numdisp > 2 ) num1 , num2 , 0 , 0 , 0 ... size of array correct, i'm having issues reassigning elements. num1 = input('input first integer of fibonacci sequence: '); num2 = input('input second integer of fibonacci sequence: '); numsdisp = input('how many integers display? --> '); list = zeros(numsdisp,1); list(1) = num1; list(2) = num2; if (numsdisp <=0) disp('value must integer greater zero.') elseif (numsdisp == 1) disp(num1) elseif (numsdisp == 2) disp(num1) disp(num2) else disp(num1) disp(num2) index = 3:numsdisp disp(list(index)) list(index) = list((index-1)) + list((index-2)); end end example input: 3 3 4 corresponding output: 3 3 0 0 you aren't printing out final result, intermediate steps. in for loop, printing list(index) updating value, rather updat...
Read more

php - MySQLi binding parameters in a prepared statement doesn't work unless inserted after "WHERE" -

-
i have php code pulls info database. uses prepared statement, works fine written below: <?php $n = '5'; if ($stmt = $connection->prepare("select title items id = ?")) { $stmt->bind_param("s", $n); $stmt->execute(); $stmt->bind_result($title); while ($stmt->fetch()) { echo $title; } $stmt->close(); } ?> and let's echo "batman". however, when move placeholder here: "select ? items id = 5" and change: $n = 'title'; instead of echoing "batman", echoes "title". not possible use placeholder select parameter? what have done this: select "title" items id = 5 you cant use prepared statement bindings identifiers, parameters.
Read more

vb.net - Alternative to the T-SQL AS keyword -

-
please see code below: imports system.data.sqlclient imports system.configuration public class form1 private _constring string private sub form1_load(byval sender object, byval e system.eventargs) handles me.load dim objdr sqldatareader dim objcommand sqlcommand dim objcon sqlconnection dim id integer try _constring = configurationmanager.connectionstrings("testconnection").tostring objcon = new sqlconnection(_constring) objcommand = new sqlcommand("select person.urn, car.urn person inner join car on person.urn = car.urn , personid=1") objcommand.connection = objcon objcon.open() objdr = objcommand.executereader(connectionstate.closed) while objdr.read id = objdr("urn") 'line 19 loop objdr.close() ...
Read more