excel - Optimization of array function that calculates products -



excel - Optimization of array function that calculates products -

i have next array formula calculates returns on particular stock in particular year:

=if(and(not(e2=e3),h2=h3),product(if($e$2:e2=e1,$o$2:o2,""))-1,"")

but since have 500,000 row entries nail row 50,000 error excel stating machine not have plenty resources compute values.

how shall optimize function works?

e column refers counter check years , ticker values of stocks. if year different previous value function output 1. output 1 when name of stock has changed. illustration may have values year 1993 , next value 1993 name of stock different, homecoming should calculated anew, , utilize 1 indication that.

then have column runs cumulative sum of 1s. when new 1 in previous column encountered add together 1 running total , maintain printing same number until observe new one. makes possible utilize of array function, if column contains running total values (e column) has next value different previous utilize twist on sumif product if. homecoming product of corresponding running total e column values.

the source of inefficiency, believe, in steady increment row number of number of cells must examined in order evaluate each successive array formula. in row 50,000, example, formula must examine cells in rows above it.

i'm big fan of array formulas, pains me this, wouldn't way. instead, utilize additional columns compute, in each row, pieces of formula needed homecoming desired result. taking approach, you're exploiting excel's efficient recalculation engine compute what's needed.

as final product, compute cumulative running product in auxiliary column, , resets value in column o when column p in row above contains number. approach much more "local" , avoids formulas depend on big numbers of cells.

i realize text not best language describing this, , poor writing skills might adding challenge, please allow me know if more detail needed.

interesting problem, thanks.

arrays excel function optimization

Comments

Post a Comment

Popular posts from this blog

model view controller - MVC Rails Planning -

model view controller - MVC Rails Planning - i'm starting rails , i'm building app stores films info, user can watch film , give grade on web application. i have coded cinema model, controller , views. now, have 2 questions: should need create model , controller grading? in case, should create or should wait , create first users model , controller? yes, on model , controller grades. that'd feasible way maintain track of who's graded what. it's create first, i'd start users, since grades depend on both films , users. ruby-on-rails model-view-controller
Read more

ruby on rails - Devise Logout Error in RoR -

ruby on rails - Devise Logout Error in RoR - while using devise in ror, encountering unusual error when trying log out url different root url. displays me : actioncontroller::invalidauthenticitytoken in devise::sessionscontroller#destroy" for illustration if rooting path after log in '/structure', , seek log out '/general' error, while logging out '/structure' fine. any help highly appreciated. ruby-on-rails ruby devise
Read more

html - Submenu setup with jquery and effect 'fold' -

html - Submenu setup with jquery and effect 'fold' - i want open submenu jquery effect fold, problem if user "hover effect" fast menu remain open, how can avoid this, jquery code is: $('ul.mainmenu li').hover( function() { $(this).children('ul').show('fold', 570); }, function() { $(this).children('ul').hide('fold', 500); } ); my jsfiddle link is: http://jsfiddle.net/9wkbf/ updated fiddle the reason behind problem is, first event $(this).children('ul').show('fold', 570); queued , until completes, sec animation not start. the next snippet can workaround $('ul.mainmenu > li').hover( function() { $(this).children('ul').show('fold', 570); }, function() { $('ul:not(.mainmenu)').hide('fold', 500); } ); *important note : work current scenario. jquery html css
Read more