This will give the count of the "B-D" rows:
=COUNTIF(E2:E19,"b-d")

This will give a count of the B-D cells where the time in column D is greater
than the time in column C (late, right)?

=SUMPRODUCT(--(E2:E19="B-d"),--(C2:C19<D2:D19))

So:

=SUMPRODUCT(--(E2:E19="B-d"),--(C2:C19<D2:D19)) / COUNTIF(E2:E19,"b-d")

Will be the percent late (format it as percent if you like).

But I'm not sure if you have times in those cells or dates and times (just
formatted to show time).

If you have date/times, then we're done.

But if you only have times, this kind of thing will be ambiguous:

23:59 0:01

Was it 2 minutes late or 23 hours 58 minutes early?

If you have those two times in A1:B1, you could use:
=B1-A1+(A1B1)
To get the 2 minute difference.
But this assumes that B1 is always after A1. And that doesn't work in your
case.

Maybe you could set a limit--no flight is every more than 4 hours late or
early???

=====
If I were doing it, I'd really try to include the dates and times in the data.

ps--about the =sumproduct() formula:

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html






wrote:

I have few transport timings. Basically, I just want to check their
ONTIME performance in terms of percentages. Is it possible to check
how many % of BEFORETIME and % of AFTERTIME?

My data is like this:

Date Trans_Name Schedule Actual Point Difference %
1-Mar E 1:35 1:41 A-B
1-Mar F 1:05 1:30 B-D
1-Mar A 0:50 1:57 D-B
1-Mar B 22:30 0:30 J-B
1-Mar D 22:15 23:02 B-D
1-Mar C 0:45 1:30 H-B
2-Mar B 1:35 1:47 A-B
2-Mar C 1:05 1:00 B-D
2-Mar E 22:30 23:55 J-B
2-Mar A 0:50 0:44 D-B
2-Mar F 22:15 22:25 B-D
2-Mar D 0:45 0:55 H-B
3-Mar D 1:05 0:45 B-D
3-Mar A 22:30 0:01 J-B
3-Mar B 1:35 1:35 A-B
3-Mar E 21:45 22:45 A-D
3-Mar F 0:45 0:55 H-B
3-Mar C 0:50 1:30 D-B

For instance, If I want to check the performance of point D-B in all
three days (1-mar to 3-mar) in terms of percentage (before time,
ontime, after time) and the deviation from the scheduled time, how
should I check it?

Thanks
Jakie

--

Dave Peterson