Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculating percentage on timevalues
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculating percentage on timevalues
Can somebody help?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculating percentage on timevalues
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculating percentage on timevalues
Actually, I want to fillup the F and G column
For example, in F2, the value should be 0:06 as there as a difference of 6 mins from the scheduled time. and in G3, the % should be 5.94%, which is the delay in percentage. I also want to add one more column "H" which will show only two values, "On/Before time" and "After Time" therefore in H3, the value should be "After Time". If I can do so for all the 19 rows, I can draw a PIVOT. But I'm not sure if you have times in those cells or dates and times (just formatted to show time). ==I just have times and NO date/times 23:59 0:01 Was it 2 minutes late or 23 hours 58 minutes early? ==It is 2 minutes late Maybe you could set a limit--no flight is every more than 4 hours late or early??? ==No flight is 5 hours late and No flight is 1 hour early is what we can set. Thanx |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculating percentage on timevalues
First, Excel will only show negative times if you have a setting toggled.
In xl2003, it's under Tools|Options|Calculation tab|check 1904 data system Be aware that if you have dates in this workbook, they'll change by 4 years and 1 day. And sharing data that contains dates between workbooks that have different base dates is troublesome, too. But I put this in F2: =(D2-C2)+((D2-C2<-20/24)) and dragged down. I used this as my test data (and my results are shown, too): Date Trans_Name Schedule Actual Point Difference 02-Mar E 01:35 01:41 A-B 00:06 02-Mar F 01:05 01:30 B-D 00:25 02-Mar A 00:50 01:57 D-B 01:07 02-Mar B 22:30 00:30 J-B 02:00 02-Mar D 22:15 23:02 B-D 00:47 02-Mar C 00:45 01:30 H-B 00:45 03-Mar B 01:35 01:47 A-B 00:12 03-Mar C 01:05 01:00 B-D -00:05 03-Mar E 22:30 23:55 J-B 01:25 03-Mar A 00:50 00:44 D-B -00:06 03-Mar F 22:15 22:25 B-D 00:10 03-Mar D 00:45 00:55 H-B 00:10 04-Mar D 01:05 00:45 B-D -00:20 04-Mar A 22:30 00:01 J-B 01:31 04-Mar B 01:35 01:35 A-B 00:00 04-Mar E 21:45 22:45 A-D 01:00 04-Mar F 00:45 00:55 H-B 00:10 04-Mar C 00:50 01:30 D-B 00:40 You can add a formula in the next column that looks at column F: =if(f2<=0,"On/Before time","After Time") I'm not sure what a percentage will give you when you're working with arrival times. If you had total flight(?) time, it would make sense to me. wrote: Actually, I want to fillup the F and G column For example, in F2, the value should be 0:06 as there as a difference of 6 mins from the scheduled time. and in G3, the % should be 5.94%, which is the delay in percentage. I also want to add one more column "H" which will show only two values, "On/Before time" and "After Time" therefore in H3, the value should be "After Time". If I can do so for all the 19 rows, I can draw a PIVOT. But I'm not sure if you have times in those cells or dates and times (just formatted to show time). ==I just have times and NO date/times 23:59 0:01 Was it 2 minutes late or 23 hours 58 minutes early? ==It is 2 minutes late Maybe you could set a limit--no flight is every more than 4 hours late or early??? ==No flight is 5 hours late and No flight is 1 hour early is what we can set. Thanx -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
getting #DIV/0! when calculating percentage | Excel Worksheet Functions | |||
calculating percentage | Excel Discussion (Misc queries) | |||
Calculating a Percentage | Excel Worksheet Functions | |||
Calculating a percentage with the end percentage in mind | Excel Discussion (Misc queries) | |||
Calculating percentage | Excel Worksheet Functions |