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

This can't be too difficult for EVERYONE... c'mon... there has to be
someone out there that knows better...


--
belly0fdesire
------------------------------------------------------------------------
belly0fdesire's Profile: http://www.excelforum.com/member.php...o&userid=25974
View this thread: http://www.excelforum.com/showthread...hreadid=393436

I understand whay your up against. I am taking a statistical class now and
the formulas that need to entered in each cell for the problems are so long
that it takes me all day just to do that. I am waiting on someone to answer
you!
--
Kathy Romines


"belly0fdesire" wrote:


This can't be too difficult for EVERYONE... c'mon... there has to be
someone out there that knows better...


--
belly0fdesire
------------------------------------------------------------------------
belly0fdesire's Profile: http://www.excelforum.com/member.php...o&userid=25974
View this thread: http://www.excelforum.com/showthread...hreadid=393436

The only thing that I know will help is if you can find a way to break
the formula up into two parts. I had an array function once and it took
ENTIRELY too long to recalculate the cells and someone helped me out
with it. They gave me two different formulas; one array and one
non-array. The way he had given this to me cut the time to recalculate
literally in half!

I'm having a little trouble following what you wrote (sorry, I'm just
tired). Could you upload an example of some sort?


--
malik641


------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=393436

The page cannot be found
The page you are looking for might have been removed, had its name
changed, or is temporarily unavailable.

--------------------------------------------------------------------------

Please try the following:

a.. If you typed the page address in the Address bar, make sure that
it is spelled correctly.

b.. Open the d42.yousendit.com home page, and then look for links to
the information you want.
c.. Click the Back button to try another link.
d.. Click Search to look for information on the Internet.



HTTP 404 - File not found
Internet Explorer



"belly0fdesire"
wrote in message
news:belly0fdesire.1tggj3_1123520928.9345@excelfor um-nospam.com...

Oops... here is the link... sorry.

http://s42.yousendit.com/e.aspx?id=0...62LAFO6X01MY1S


--
belly0fdesire
------------------------------------------------------------------------
belly0fdesire's Profile:
http://www.excelforum.com/member.php...o&userid=25974
View this thread: http://www.excelforum.com/showthread...hreadid=393436

Here is a link to a zip file containing the spreadsheet I need help
on..... maybe this will help me get an answer? Please someone just
look at it and let me know if you have a better way. Thank you.


--
belly0fdesire
------------------------------------------------------------------------
belly0fdesire's Profile: http://www.excelforum.com/member.php...o&userid=25974
View this thread: http://www.excelforum.com/showthread...hreadid=393436

Oops... here is the link... sorry.

http://s42.yousendit.com/e.aspx?id=0...62LAFO6X01MY1S


--
belly0fdesire
------------------------------------------------------------------------
belly0fdesire's Profile: http://www.excelforum.com/member.php...o&userid=25974
View this thread: http://www.excelforum.com/showthread...hreadid=393436

lets try this again....

http://s42.yousendit.com/d.aspx?id=0...62LAFO6X01MY1S


--
belly0fdesire
------------------------------------------------------------------------
belly0fdesire's Profile: http://www.excelforum.com/member.php...o&userid=25974
View this thread: http://www.excelforum.com/showthread...hreadid=393436