excel - Sum Column based on prior 12 columns' row entries (rolling count) -


really stuck on one. please see picture. have data table multiple rows (the real data i'm working has 2,000+ rows).enter image description here

i need sum row entries in each column, need put sums in 2 different categories: "new", , "return". new starts @ first date value appears, , it's considered new next 12 months (so 10/1/2005 - 10/1/2006). value type after initial 12 month window considered "return". dates first value appears @ different though, each type (rock, paper, scissors), there's not 1 static date can measure from, each row first value appear @ date.

so if value occurs, value occurs 9 months later, both values "new". after 12 months since first occurrence become "return".

any hugely appreciated. can manually, on 2,000 rows take forever , lead lot of errors. solution helpful- conditional format, formula, vba. hope it's clear. again.

wow!! - 1 doozie me, can done using array formulas. (i wanted see if - nicer solution, though, in vba)

the way is, suppose set data have in sample picture - such 10/1/2005 data in column b. so, see how yearly, suppose you're working on column 2/1/2007 (given example, in column r.

so, return calculation (in cell r6) follows:

=sumproduct(--(mmult(if(f2:q4="",0,f2:q4),transpose({1,1,1,1,1,1,1,1,1,1,1,1}))>0),r2:r4)

note this must entered array function (using ctrl + shift + enter once entered) work.

then, new calculation sum of column minus returns. so, in cell r5, have:

=sum(r2:r4)-r6

(entered normal formula).

as simple explanation, you're using matrix multiplication multiply values in each row 1 , summing on per-row basis (the mmult() part), you're examining if returns >0 - meaning there @ least single value in row last 12 months.

then, if there value, add in current month's value return sum (this done using sumproduct() function)... gives need.

the nice thing is number of rows of data, situation should satisfied, bad thing processor-intensive, work!!

i hope @ least helps set on way!


Comments

Post a Comment

Popular posts from this blog

apache - Remove .php and add trailing slash in url using htaccess not loading css -

i have add following code htaccess file removing .php extension , add trailing slash urls. worked fine reduced page loading speed , css code not loading @ all. pages ugly. please help rewriteengine on rewritebase / ## hide .php extension snippet # externally redirect /dir/foo.php /dir/foo rewritecond %{the_request} ^[a-z]{3,}\s([^.]+)\.php [nc] rewriterule ^ %1/ [r,l] # add trailing slash rewritecond %{request_filename} !-f rewritecond %{request_uri} !/$ rewriterule . %{request_uri}/ [l,r=301] # internally forward /dir/foo /dir/foo.php rewritecond %{request_filename} !-d rewritecond %{request_filename}.php -f rewriterule ^(.*?)/?$ $1.php [l] we can rewrite urls using .htaccess files. before writing in .htaccess files need verify kind of server using. can check server adding a <?php phpinfo(); ?> in php page. ensure mod_rewrite module enabled in apache server. can identified checking phpinfo included page. common methods used url rewriting follows ...
Read more

javascript - jQuery show full size image on click -

pretty self explanatory not workking . i want display full image when user click on image... yes know there tons of plugins but i want keep simple. any idea ? css : .full { display: none; width: 500px; height: 500px; margin-left: 100px; margin-top: -300px; position: fixed; background-color: #f00; z-index: 6; } .thumbnail { width: 50px; height: 50px; } html: <img class="thumbnail" src="http://4.bp.blogspot.com/-uhbdp2esxng/txneptvi92i/aaaaaaaaaky/upfqis7zto0/s1600/rihanna-wallpaper.jpg"> jquery: $(document).ready(function() { $('.thumbnail').on('click', function() { var img = $('<img />',{src:this.src,'class':'full'}); $('.full').html(img).show(); console.log('done!'); }); }); try looking how jquery works. $(document).ready(function() { $('.thumbnail').on('click', function(...
Read more