performance - How to gauge Excel Calculation speed? -

-

i developing excel 2010 application, contains complex calculations in on 60+ worksheets. when change data in cell, takes lot of time in background calculation (i want calculation automatic).....

  1. is there way find out formula taking more time on other?
  2. what better approach improve performance - multiple simple formulas vs. single complex (multistep) formula?

i.e.
[step-1] e1 = c1 * d1
[step-2] f1 = e1 / b1
[step-3] g1 = f1 + b1

or

[single step] g1 = (c1 * d1 / b1) + a1

suggestion appreciated!
thanks

as second part, if use ordinary non-volatile functions multiple simple formulas better 2 reasons:

  1. on simple recalculations (without rebuilding dependency trees) excel calculate parts changed, e.g. in single step example if value in a1 changes excel have recalculate expression in parentheses (c1 * d1 / b1) if values of c1, d1, b1 unchanged. when replace part reference f1, value of f1 not recalculated if a1 changes value.
  2. multiple simple formulas better calculated in parallel if have multiple cores.

another usefull link in addition msdn: http://www.decisionmodels.com/calcsecretsc.htm

volatile functions evil in large workbooks, offset , indirect. recalculated every time changes in file, , calculated in single thread. cell depends on cell volatile function becomes volatile well, because dependencies have recalculated every time volatile function recalculated. viral volatility in big file damage performance. using many simple formulas helps in case well, since many dependencies remain non-volatile.

from link above:

some excel features not use multithreaded calculation, example:

data table calculation (but structured references tables use mtc). user-defined functions (but xll functions can multithread-enabled). xlm functions. indirect, cell functions use either format2 or address options. getpivotdata , other functions referring pivottables or cubes. range.calculate , range.calculaterowmajororder. cells in circular reference loops.

once upon time inherited big file took 30 min recalculate on dedicated fast machine , due crazy usage of offsets access data big sheet. moving calculation logic excel access , importing results via pivot table reduced total calculation time several seconds!


Comments

Post a Comment

Popular posts from this blog

ios - UICollectionView Self Sizing Cells with Auto Layout -

-
Image
i'm trying self sizing uicollectionviewcells working auto layout, can't seem cells size content. i'm having trouble understanding how cell's size updated contents of what's inside cell's contentview. here's setup i've tried: custom uicollectionviewcell uitextview in contentview. scrolling uitextview disabled. the contentview's horizontal constraint is: "h:|[_textview(320)]", i.e. uitextview pinned left of cell explicit width of 320. the contentview's vertical constraint is: "v:|-0-[_textview]", i.e. uitextview pinned top of cell. the uitextview has height constraint set constant uitextview reports fit text. here's looks cell background set red, , uitextview background set blue: i put project i've been playing on github here . updated ios 9 after seeing github solution break under ios 9 got time investigate issue fully. have updated repo include several examples of different configurations s
Read more

node.js - ldapjs - write after end error -

-
i have created authentication service using following code in node.js , ldapjs. var when = require ('when'); var authenticationerror = require('../errors/authenticationerror'); var sessionmanagerservice = require('./sessionmanagerservice'); var ldap = require('ldapjs'); var client = ldap.createclient({ url: 'ldaps://ad.mycompany.com:636', tlsoptions: {'rejectunauthorized': false} }); module.exports = { signin: function (email, password) { return this.ldapbind(email, password).then( function () { return sessionmanagerservice.createsessionhash({email: email}); } ); }, ldapbind: function (email, password) { var deferred = when.defer(); client.bind(email, password, function(err) { if (err) { deferred.reject (new authenticationerror('invalid username and/or password!', 'authentication.signin.error')
Read more

DOM Manipulation in Wordpress (and elsewhere) using php -

-
DOM Manipulation in Wordpress (and elsewhere) using php - i'm quite new dom manipulation world , head start in order avoid mutual errors. i looking efficient way manipulate content generated wordpress using php. @ moment using simple html dom seems work fine. found domdocument , few others , helpfull if cleared out 1 faster, improve or @ to the lowest degree generate less errors in case of bad markup. also on side can explain syntax (instead of $html @$html) thank much php wordpress domdocument simple-html-dom
Read more