LinkBack URL
About LinkBacksMaybe you could replace the arrays with a formula for each criteria,
=AND(C2>=B1,C2<=C1,D2=A1,and so on) for each line
then to count just use
=COUNTIF(formula_range,TRUE)
note that I didn't use any sheet names but in general it is faster to test
each row/column and the count the result of those formulas
here's a link
http://www.decisionmodels.com/optspeedb.htm
--
Regards,
Peo Sjoblom
(No private emails please)
"belly0fdesire" <[email protected]> wrote in message
news:[email protected]...
> 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.
> --
> We are the music makers... and we are the dreamers of dreams.
Register To Reply
Bookmarks