Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Problem with SUMPORDUCT formula
I have more info in my other post (Tracking sheet help) & (sumproduct
function, Bob P please see tracking sheet help). I'm using this in Sheet 2 (LTC) =IF(D3="","",D3-C3) and this in sheet 3 (OTP) =SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192<=--"0:10"),--(LTC!$E$3:$E$192<"")) =SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192=--"0:10"),--(LTC!$E$3:$E$192<"")) I"m counting vehicles that pass a location. Sheet 2 (LTC) column C is the time they are scheduled to pass. Column D is the time I enter that they actually pass. Column E sheet 2 is set to auto calculate the difference, =IF(D3="","",D3-C3) Sheet 3 is used for ON TIME PERFORMANCE (OTP). If they pass from within 0 to 10 minutes I count them in column B of sheet 3 (OTP), and if they pass 11 or more minutes late I count them in column C of sheet 3 (OTP). The left 4 digits in column F Sheet 2 (LTC) are the same as column A sheet 3 (OTP) My problem is the formula is not placing the count in the right column if the difference = 10 & and if I change the formula to 11 the same occurs. Would there be a way to have Sheet 3 set up to do the math. Similar to the formula on sheet 2. =IF(D3="","",D3-C3) and if the total = under 0 to 10 minutes it will be entered in Sheet 3 column B and if the total was 11 minutes or more the count would be entered in sheet 3 column C? (I said Under because sometimes they are allowed to pass before there scheduled time so I end up with a negative that I have to manually enter (3-) or (4-),(5-) in column E sheet 2, which further complicates the sheet, I don't think there is a way to count or show negative time). Hope this makes some sort of sense. Example sheet 2(LTC) A B C D E F Run Vehicle Time Actual Diff Schedule 924 0 4:48 4:59 0:11 535S0448 208 0 5:13 5:23 0:10 401S0459 904 0 5:18 5:28 0:10 535S0518 908 0 5:25 511S0518 Example sheet 3 (OPT) A B C Route 0-10 +10 401n 0 0 401s 0 0 402n 0 0 402s 0 0 511n 0 0 511s 0 0 535n 0 0 535s 0 0 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Problem with SUMPORDUCT formula
I discovered that this formula doesn't want to repeat the same calculation
back to back. Even using just the one that counts if<=--"0:10". And if I end up with 10 minutes difference to start, in this case (Cell E 2) it won't count the next one that is 10 minutes difference, (Cell E 3). Of course you could test this to verify, Run # Vehicle # Time Actual Diff Schedule 28 0 6:18 6:28 0:10 511S0610 20 0 6:37 6:43 0:06 511N0605 32 0 6:48 6:58 0:10 511S0640 26 0 7:07 7:10 0:03 511N0635 20 0 7:18 7:20 0:02 511S0710 28 0 7:37 7:45 0:08 511N0705 26 0 7:48 7:49 0:01 511S0740 32 0 8:07 8:11 0:04 511N0735 28 0 8:18 8:23 0:05 511S0810 62 0 8:18 8:24 0:06 535S0818 20 0 8:37 8:43 0:06 511N0805 32 0 8:48 8:55 0:07 511S0840 64 0 9:05 9:08 0:03 535N0821 26 0 9:07 9:10 0:03 511N0835 20 0 9:18 9:28 0:10 511S0910 And the results from this are, Route 0-10 +10 510n 510s 511n 6 511s 6 513n 513s 532n 0 532s 0 What do you think? If I start the first entry like below, here are the results. 28 0 6:18 6:20 0:02 511S0610 20 0 6:37 6:40 0:03 511N0605 32 0 6:48 6:53 0:05 511S0640 26 0 7:07 7:10 0:03 511N0635 20 0 7:18 7:23 0:05 511S0710 28 0 7:37 7:45 0:08 511N0705 26 0 7:48 7:52 0:04 511S0740 32 0 8:07 8:10 0:03 511N0735 28 0 8:18 8:19 0:01 511S0810 62 0 8:18 8:20 0:02 535S0818 20 0 8:37 8:43 0:06 511N0805 32 0 8:48 8:55 0:07 511S0840 64 0 9:05 9:15 0:10 535N0821 26 0 9:07 9:17 0:10 511N0835 20 0 9:18 9:28 0:10 511S0910 Route 0-10 +10 510n 510s 511n 6 511s 7 513n 513s 532n 0 532s 0 This is correct, and if I go to the top and and change the entry after going this far down the list it will count correctly. and this is only the one fumula <=--"0:10" Is this something that can be solved? "Dale G" wrote: I have more info in my other post (Tracking sheet help) & (sumproduct function, Bob P please see tracking sheet help). I'm using this in Sheet 2 (LTC) =IF(D3="","",D3-C3) and this in sheet 3 (OTP) =SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192<=--"0:10"),--(LTC!$E$3:$E$192<"")) =SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192=--"0:10"),--(LTC!$E$3:$E$192<"")) I"m counting vehicles that pass a location. Sheet 2 (LTC) column C is the time they are scheduled to pass. Column D is the time I enter that they actually pass. Column E sheet 2 is set to auto calculate the difference, =IF(D3="","",D3-C3) Sheet 3 is used for ON TIME PERFORMANCE (OTP). If they pass from within 0 to 10 minutes I count them in column B of sheet 3 (OTP), and if they pass 11 or more minutes late I count them in column C of sheet 3 (OTP). The left 4 digits in column F Sheet 2 (LTC) are the same as column A sheet 3 (OTP) My problem is the formula is not placing the count in the right column if the difference = 10 & and if I change the formula to 11 the same occurs. Would there be a way to have Sheet 3 set up to do the math. Similar to the formula on sheet 2. =IF(D3="","",D3-C3) and if the total = under 0 to 10 minutes it will be entered in Sheet 3 column B and if the total was 11 minutes or more the count would be entered in sheet 3 column C? (I said Under because sometimes they are allowed to pass before there scheduled time so I end up with a negative that I have to manually enter (3-) or (4-),(5-) in column E sheet 2, which further complicates the sheet, I don't think there is a way to count or show negative time). Hope this makes some sort of sense. Example sheet 2(LTC) A B C D E F Run Vehicle Time Actual Diff Schedule 924 0 4:48 4:59 0:11 535S0448 208 0 5:13 5:23 0:10 401S0459 904 0 5:18 5:28 0:10 535S0518 908 0 5:25 511S0518 Example sheet 3 (OPT) A B C Route 0-10 +10 401n 0 0 401s 0 0 402n 0 0 402s 0 0 511n 0 0 511s 0 0 535n 0 0 535s 0 0 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula problem | Excel Worksheet Functions | |||
Formula problem | Excel Discussion (Misc queries) | |||
Formula problem | Excel Discussion (Misc queries) | |||
Formula Problem | Excel Worksheet Functions | |||
formula Problem | Excel Discussion (Misc queries) |