How to solve a complex problem using Excel 2003

[Guest]

Hi,

I need the help of an expert here:

I have a table of data that shows the routings of documents assigned to
different departments in a company, as each document proceeds through the
approval process from person to person. The table displays the information
about each document in the following order: department name (Dept), document
number (Doc), routing number (Routing), the date the document was routed to a
location (Date), the location it was routed to (Location), and the number of
days it spent at that particular location (# of Days). The table looks like
this:

Dept. Doc. Routing Date Location # of Days
D1 123 1 20/04/07 M 6
D1 123 2 30/04/07 W 0
D1 123 3 30/04/07 M 2
D1 123 4 02/05/07 A 2
D1 123 5 04/05/07 M 0
D1 123 6 04/05/07 W 0
D1 123 7 04/05/07 W 0
D1 123 8 04/05/07 C 1
D1 123 9 07/05/07 X 21
D1 123 10 06/06/07 W 0
D1 123 11 06/06/07 M 1
D1 123 12 07/06/07 W 0
D1 123 13 07/06/07 C 0
D1 123 14 07/06/07 M 13
D1 123 15 26/06/07 A 0
D1 123 16 26/06/07 M 1
D1 123 17 27/06/07 W 2
D1 123 18 29/06/07 C 0
D1 123 19 29/06/07 X 5
D1 123 20 09/07/07 C 0
D1 123 21 09/07/07 M 0
....etc.

In my company, Documents are only supposed to be routed once to location X,
which is the CEO's office, but sometimes they get sent back for revision.

What I would like to do is get Excel to calculate how many extra days
documents spend in the various locations AFTER they been sent back to
location X for revision, so that I can factor this out of the overall
turnaround times for company documents at each location in the approval
process.

Basically, I need Excel to identify the range of cells associated with each
particular document, then determine if the range contains two or more entries
of X in the Location field, then display the number of days each document has
spent in each location IN BETWEEN routings to location X. This may have to
iterate within the range for situations where a document was routed to X more
than two times.

So far, I have been unsuccessful at creating a formula that captures this
information. I have tried using a combination of IF statements and LOOKUP
functions, but can't get them to work.

Any suggestions?

Thanks,

Marcolino

 

[Guest]

one or two helper columns might do what you need
if your current data is in A:F
in G3 enter
=if(e2="X","X","")&G2

sumif(G:G,"X",F:F) will give the number of days between the first ans second
visit to X
and
sumif(G:G,"XX",F:F) will give the number of days between the second and
third visit

 

[Guest]

Thanks for getting back to me so quickly.

The problem is, the full table includes data from over 100 documents, each
with different numbers of routings and different locations (there are some
commonalities, such as that every document must be routed to location X at
some point in time).

How do I get Excel to start from zero at the first routing of each new
document and then re-perform the calculations you've suggested?

Thanks again,

Marcolino