Calculating whether solution is in time or not

T

The Fool on the Hill

Dear Excel(lent) users,

I have a list in which al sent requests are logged. Every request has a
priority, each priority defines in what time a request needs to be executed.

To clarify:
In Column A I use the date of the sent request
In Column B I use the date of the request being executed
In Column C I use the priority of the request

Column A Column B Column C
-------------- ------------- ----------
01-01-2008 02-01-2008 1
01-02-2008 02-02-2008 2
01-03-2008 02-03-2008 3
01-04-2008 08-04-2008 1
01-04-2008 08-05-2008 4
01-04-2008 08-06-2008 5

If you take the following maximum processing time into consideration:

Priority max days to process
-------- -----------------------
1 1
2 2
3 5
4 10
5 15

I am looking for a formula which would give me the following information:

-------- --------------- --------------------
1 1 1
2 1 0
3 1 0
4 0 1
5 0 1

Dear Excel(lent) users,

I have a list in which al sent requests are logged. Every request has a
priority, each priority defines in what time  a request needs to be executed.

To clarify:
In Column A I use the date of the sent request
In Column B I use the date of the request being executed
In Column C I use the priority of the request

Column A       Column B     Column C
--------------   -------------   ----------
01-01-2008    02-01-2008   1
01-02-2008    02-02-2008   2
01-03-2008    02-03-2008   3
01-04-2008    08-04-2008   1
01-04-2008    08-05-2008   4
01-04-2008    08-06-2008   5

If you take the following maximum processing time into consideration:

Priority    max days to process
--------    -----------------------
1            1
2            2
3            5
4            10
5            15

I am looking for a formula which would give me the following information:

--------        ---------------        --------------------
1                         1                             1
2                         1                             0
3                         1                             0
4                         0                             1
5                         0                             1

Maybe...

=SUMPRODUCT(--(\$B\$2:\$B\$7-\$A\$2:\$A\$7<=VLOOKUP(E2,\$I\$2:\$J\$6,2,FALSE)),--
(\$C\$2:\$C\$7=E2))

in F2, filled down to F6, for Deadline met. Priorities 1-5 in E2:E6
and...

=COUNTIF(\$C\$2:\$C\$7,E2)-F2

in G2 filled down to G6 for deadline not met.

Lookup table for Priority/max days to process in \$I\$2:\$J\$6.

Ken Johnson

I put the original data with dates in cells A1:C6. I put you Priority in
cells A10:B14. then I added to the priority table columns C and D (C10:C14)

These are the formulas fro cells C10 and D10

=SUMPRODUCT(--(\$C\$1:\$C\$6=\$A10),--(\$B\$1:\$B\$6-\$A\$1:\$A\$6<=\$B10))
=SUMPRODUCT(--(\$C\$1:\$C\$6=\$A10),--(\$B\$1:\$B\$6-\$A\$1:\$A\$6>\$B10))

Thank you very much !

Ken Johnson said:
Maybe...

=SUMPRODUCT(--(\$B\$2:\$B\$7-\$A\$2:\$A\$7<=VLOOKUP(E2,\$I\$2:\$J\$6,2,FALSE)),--
(\$C\$2:\$C\$7=E2))

in F2, filled down to F6, for Deadline met. Priorities 1-5 in E2:E6
and...

=COUNTIF(\$C\$2:\$C\$7,E2)-F2

in G2 filled down to G6 for deadline not met.

Lookup table for Priority/max days to process in \$I\$2:\$J\$6.

Ken Johnson

Thank you very much !

Joel said:
I put the original data with dates in cells A1:C6. I put you Priority in
cells A10:B14. then I added to the priority table columns C and D (C10:C14)