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.
--
belly0fdesire
------------------------------------------------------------------------
belly0fdesire's Profile:
http://www.excelforum.com/member.php...o&userid=25974
View this thread:
http://www.excelforum.com/showthread...hreadid=393436