Hi all, sorry to make my first post a question. I have an excel file (see attached) in which data is collected on individual units which are processed within a 3 day interval, where on the 4th day it should be 'approved' (4th day overlaps with 1st day of next interval).
I have array formulae on the 'Interval Dates' Tab which summarise this data into whether the unit was approved on time for it's interval, the total number within the interval, and the number approved within an interval. This output is what I want, but far too slow to calculate with larger amounts of data and interval dates.
Is it possible to represent this in a PivotTable (which should be a lot quicker)? I've not used PivotTables much before, and cannot get it to effectively 'countif' to look for a Y in the On Time column (hence I can only get it to calculate as 100% on time).
If anyone can point me in the right direction for getting a PivotTable to do what I want, I would be grateful. If it is not possible, can somebody suggest what might calculate quicker? I'm guessing DCount or code my own macro.
Otherwise the only solution is likely to be seperating the array formulae into a seperate summarising excel file so the array formulae aren't updated at every change to the other worksheets.
As a heads up, I am using WORKDATE and NETWORKDAYS, so ensure you have Analysis ToolPak on!
Bookmarks