Loop with multi-row formula, maybe with Marco? Please help!

[JoyHe]

Hi Guys, 

I need to deal a data which measure the work efficiency, that is compare the tasks finished everyday and all tasks open that day and from previous days' remaining.

So the calculation today will depend on that of yesterday cause I will include tasks haven't done before into TODAY's OPENING, I try to figure out the logic but I have no idea about looping it. 

Please see attached workflow in which I try to show my logic. I am wondering is it possible to finish it inside an interactive macro? I tried but failed.

Really appreciate your time!

[Christina_H · Accepted solution]

I'm not entirely clear on what you're trying to do, but can you use running sums instead?

[danilang]

Hi @JoyHe 

If you're goal is to tell you the number of open jobs [Open_total] at the end of each day, you can do it in single Multi-Row formula tool with the following equation

[sum_open]-[sum_close] + [Row-1:open_total]

This takes the difference between the number of jobs opened and closed today and adds it to yesterday's [open_total].  The results look like this

for each day, the open total is increased/decreased by the difference between the number of jobs open on that day and the number closed on that day. 

day 1=7.   

day 2=14.  7 from today plus 7 from yesterday

day 3=12  -2 from today, since 2 jobs were opened but 4 were closed plus the 14 from yesterday

etc.

Dan  

[binay2448 · Accepted solution]

May be this will help for your requirement...