LinkBack URL
About LinkBacks
Array Formulas take waaaay too long...
Okay. I have never had a class in excel or read a book and have had to figure out ways of doing things on my own. This project is INSANE. I have a very complicated spreadsheet that I was assigned. I will try my best to explain this:
There are several different offices which all have different number series ranges for their files. For instance, Imperial "C" has 2010000 - 2059999, but Inland Empire "C" has 2800000 - 2899999 and 2600000 - 2699999, as well as 7 more different, gapped number series ranges for just that office. There are 8 different offices for "C". The "C" is a company code. There is also a company code "L" and there are also 8 different offices for that company. Company code "C" will only have one office assigned to a number series, but company code "L" may have a number series assigned to it that overlaps a number series for company code "C". "L" will not overlap "L" and "C" will not overlap "C", but "L" may overlap "C" and vice versa. Files are recorded on one day, then recieved by us, then processed and sent back to the same office that sent them to us. The dates, company codes and order numbers are in a sheet that is defined by a database query to an Access Database that users enter the information into. My mission is to determine how long offices are taking to send us the packets (Recorded Date to Recieved Date) broken down like: Less Then 5 Days, 6 - 10 days, 11 - 15 days and so on all the way up to 31+ days. Also how long it takes us to send the packets back to the office after we recieve them (Received Date to Sent Back Date) broken down by Less Than 30 Days and then then by weeks (I used days in my formulas to make it easier) all the way up to 10 Weeks +.
I set up one sheet for the user to select from a combo box the office of the information they want to see and all the information is displayed below. The formulas below are all just sums of the formulas in the "FS" sheet I talk about later.
I set up another sheet (Ranges) to only contain a definition of what number series ranges apply to which offices. The first column of this sheet contains the L or C and the second column contains the name of the office. Columns C through T contain number series range beginning and endings for each office. Column C is a beginning number, D is an ending number, E is a beginning number, F is an ending number and so on.
Another sheet (FS) is where all my array formulas are. The first row is dedicated to lookups. Using the combo box on the first sheet, the user selects the office they want to see and the the lookup formulas look at the Ranges sheet to determine what Ranges and company code applies to that office. These formulas go all the way over to U1 and if an office, such as Imperial has only one number range series, the remaining cells are filled with 0's. A few rows beneath this are my array formulas. Below is an example of one of the array formulas. This one is used to determine how many files were sent to us within 6 to 10 days of its recording date using the first number series range. I then copied and pasted the formula two columns to the right to get the count for the next number series range and so on until I had the count for every number series range. The totals of these are displayed on the first page. RPL is the name of the sheet containing (in this order) A=Received Date, B=Recording Date, C=File Number, D=Company Code, E=Box# (irrelevant), F=To_IC (irrelevant), G=From_IC (irrelevant), H=Back_to_Site. FS!$A$1 is "C" or "L".
{=COUNT(IF(RPL!$C$2:$C$15160>=FS!B1,IF(RPL!$C$2:$C$15160<=FS!C1,IF(RPL!$D$2:$D$15160=FS!$A$1,IF(RPL!$A$2:$A$15160-RPL!$B$2:$B$15160>=6,IF(RPL!$A$2:$A$15160-RPL!$B$2:$B$15160<=10,IF(RPL!$C$2:$C$15160<>0,RPL!$C$2:$C$15160)))))))}
This is my array formula for determining a count our turnaround time for sending packets Back_to_Site after they have been received that was from 31 to 35 days. It is then continued across to U just like the other array formula to calculate for all number range series possibilities and just like the other array formulas is then adjusted in the rows below for 36 to 40, 41 to 50 and so on:
{=COUNT(IF(RPL!$C$2:$C$15160>=FS!B1,IF(RPL!$C$2:$C$15160<=FS!C1,IF(RPL!$D$2:$D$15160=FS!$A$1,IF(RPL!$H$2:$H$15160-RPL!$A$2:$A$15160>=31,IF(RPL!$H$2:$H$15160-RPL!$A$2:$A$15160<=35,IF(RPL!$C$2:$C$15160<>0,RPL!$C$2:$C$15160)))))))}
Still alive? My formulas work fine and my counts come back accurately, but the array formulas take so long to calculate that I need to know if there is a better way of going about this that takes less time to calculate. PLEASE SOMEONE ANSWER ME! Thank you for reading this.
Register To Reply
Bookmarks