Array formula alternative

[ajhmartin903]

I have a spreadsheet that I use to track time logs against projects, for my entire team. I track this on a weekly basis. Unfortunately, the tool we use doesn't give me the breakdown of change each week. It just gives me the total time spent against the project.

Given this, each week I export all the issues to Excel (whether open or closed), along with the total time logged against them. The next week, I export all the same issues again, and any new ones, and use an array formula to look at the new week's total time spent, look at the old week's total time spent, calculate the difference, and work out how much time was actually spent in that week on that project. This allows me to track over a longer period of time how much effort we're spending on each project week in week out.

My formula as it stands is:
{=IF([@[Story Type]]="New", [@[Time Logged Total]], INDEX([Time Logged Total], MATCH([@Week] & [@[Issue key]],[Week] & [Issue key], 0)) - INDEX([Time Logged Total], MATCH(([@Week]-1) & [@[Issue key]],[Week] & [Issue key], 0)))}

To translate this, it is:

• If the story is newly created, take whatever the time log total is and use it as the time log for this week as well.
• Otherwise, look at the total time log, look at the previous entry for that story and take that time log, subtract them, and use the result as the time log for this week.

The formula is quite slow. I've probably 10,000 rows at this point in the overall spreadsheet. It takes maybe 10 seconds to calculate, but given there are so many pivot charts and the like that are coming off this data, it slows the entire spreadsheet down.

I'm certainly no Excel expert and cobbled the above together through my own knowledge and guidance I found online. Can anyone recommend a more efficient formula, or tweaks to the existing formula, to improve performance?

 

[Haui]

Easiest may be to work with helper columns, e.g.: in one column pull the historical time spent, in another the new total, in another the difference: this should remove the need for arrays and dramatically speed up calculations