Excel - function that returns a matrix from a reference to its centre and radius -


i performing matrix operations sumproduct. have convolution kernel apply dataset. however, have field somewhere defines kernel size.

now, based on kernel size, have several convolution kernels, think can in way via vlookup. however, that's convolution kernel, it's not data matrix.

currently, wrote sumproduct(convolutionkernel!$a$1:$c$3;data!h8:j10) in cell i9 of convolution result. write similar :

sumproduct(make_matrix(convolutionkernel!$b$2; 2); make_matrix(data!j8; 2))

(in reality, $b$2 here vlookup thingy). 2 kernel size - make_matrix(xn; 1) should return xn cell, make_matrix(xn, 2) should return x-1 n-1:x+1 n+1, etc.

the offset function

this volatile function. ok use long not dealing large number of volatile functions in workbook or volatile function not dealing large amounts of data in calculations.

offset composed of 5 parts:

offset( a, b, c, d, e)

  • a reference point rest of offset function. not have on same worksheet being called. need put in right address format needs.
  • b how many rows reference point want move. 0 keep on reference point. negative integers move many rows reference point. positive integers move down many rows.
  • c how many columns reference point want move using same method b above.
  • d number of rows want pull or work with. 0 means wont pull information. 1 pull row working with, 2 pull 2 rows.
  • e number of columns want pull or work , works in same manner d above.

if offset entered in single cell display first element if pulled 3x3 area of information. can use offset replace range/address in function calculation.

the index function

now if building out matrix on sheet talked , know maximum size matrix be, use following non-volatile function pull matrix using index function:

=index($b$2:$ss$513,a1,b1)

where a1 number of rows work counting top, , b1 number of columns work counting left. since want square matrix pulled, a1=b1, following used:

=index($b$2:$ss$513,a1,a1)

$b$2:$ss$513 maximum size of matrix working with. full sheet reference use then may run problem maximum cell calculations. quicker if can define maximum range might working with.

update ii

the example below generic 15x15 matrix coordinate relative center, j9, being each of individual cell values. note dumped size of square matrix returned in b2. used index formulas below determine address of top left , bottom right corner since these how define range.

top left

=index(1:1048576,row($j$9)-($b$1-1)/2,column($j$9)-($b$1-1)/2)

bottom right

=index(1:1048576,row($j$9)+($b$1-1)/2,column($j$9)+($b$1-1)/2)

j9 centre generic matrix

now 1:1048576 whole sheet reference. reduced maximum matrix size j9 centre point of $b$1:$r$17. used full sheet reference since had 2 difference size matrix deal , need define centre point either (j9=>new point). work if centre point not change.

now index kind of interesting returns cell address , displays whatever @ cell address. such if want sumproduct(matrix1, matrix2), can following assuming example matrix matrix one:

=sumproduct(index(1:1048576,row($j$9)-($b$1-1)/2,column($j$9)-($b$1-1)/2):index(1:1048576,row($j$9)+($b$1-1)/2,column($j$9)+($b$1-1)/2),matrix2)

note ":" , how being used separate 2 index formulas above. same saying g6:m12 when set value of b1 7.

if understood correctly , matrices have constant centre point use above method , either need supply n value in b1 or calculate n suit needs.

caveat: in order matrix have centre, n or value in b2 need odd integer.


Comments

Post a Comment

Popular posts from this blog

jOOQ update returning clause with Oracle -

according http://www.jooq.org/doc/3.8/manual/sql-building/sql-statements/update-statement/ jooq doesn't support returning clause in update statements databases except firebird , postgres. does know, still correct oracle jooq? according oracle's documentation, oracle db supports returning clase delete, insert, or update statements. incorrect manual you're right - manual wrong , should fixed: https://github.com/jooq/jooq/issues/5470 single row update returning jooq 3.8 added support single row update .. returning statements. internally, uses callablestatement , pl/sql block of form: begin update "my_table" set "my_table"."column" = 'xyz' "my_table"."id" = 1 returning "my_table"."id", "my_table"."column" ?, ?; ? := sql%rowcount; end; the relevant issue is: https://github.com/jooq/jooq/issues/5190 multi row update returning a...
Read more

java - Warning equals/hashCode on @Data annotation lombok with inheritance -

i have entity inherits other. on other hand, i'm using lombok project reduce boilerplate code, put @data annotation. annotation @data inheritance produces next warning: generating equals/hashcode implementation without call superclass, though class not extend java.lang.object. if intentional, add '@equalsandhashcode(callsuper=false)' type. is advisable add annotation @equalsandhashcode (callsuper = true) or @equalsandhashcode (callsuper = false) ? if not added, 1 callsuper=false or callsuper=true ? the default value false . 1 if don't specify , ignore warning. yes, recommended add @equalsandhashcode annotation on @data annotated classes extend else object. cannot tell if need true or false , depends on class hierarchy, , need examined on case-by-case basis. however, project or package, can configure in lombok.config call super methods if not direct subclass of object. lombok.equalsandhashcode.callsuper = call for configuration system ...
Read more

java - BasicPathUsageException: Cannot join to attribute of basic type -

i using java 8 jpa (hibernate 5.2.1). have clause works perfectly, until introduce clause make use foreign key table values too. i getting following error: basicpathusageexception: cannot join attribute of basic type i think problem related fact have join table, , not sure how create join using join table. employee - employee_category - category model (employee.java): @manytomany(cascade = cascadetype.all, fetch=fetchtype.eager) @jointable ( name="employee_category", joincolumns={ @joincolumn(name="emp_id", referencedcolumnname="id") }, inversejoincolumns={ @joincolumn(name="cat_id", referencedcolumnname="id") } ) private set<category> categories; model (category.java): @id private string id; jpa when introduce following code, fails error above (i think problem new piece of code): join<t, category> category = from.join("id"); predicates.add(criteriabuilder.like(category....
Read more