Calculating whether solution is in time or not

  • Thread starter Thread starter The Fool on the Hill
  • Start date Start date
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:

Priority Deadline met Deadline not met
-------- --------------- --------------------
1 1 1
2 1 0
3 1 0
4 0 1
5 0 1


Can you please help me ??
 
 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:

Priority        Deadline met        Deadline not met
--------        ---------------        --------------------
1                         1                             1
2                         1                             0
3                         1                             0
4                         0                             1
5                         0                             1

Can you please help me ??
Click to expand...

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)
the deaddline Met and Deadline not Met.

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
Click to expand...
 
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)
the deaddline Met and Deadline not Met.

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))
Click to expand...
 
Back
Top