You can simplify it to
=IF(OR(NOT(E142),NOT(E141)),SUM(E133:E142,F141)-SUM(F132:F141,E141),"")
--
---
HTH
Bob
(change the xxxx to gmail if mailing direct)
"Rob" wrote in message
...
Hello,
I'm trying to do three things here...
1: Add the total time that someone had a document in their possestion for
review (individually)
2: Add the total time for row 141 Plus the inbetween time of all the
other
times from when rcvd to sent
3: Add all the time put together
Below is the formula I am using but I have issues with it, especially when
I
try to convert it to VB so That I can insert row 141's formula
automatically.
Cell Formula
=IF(E142<FALSE,SUM((E133-F132)+(E134-F133)+(E135-F134)+(E136-F135)+(E137-F136)+(E138-F137)+(E139-F138)+(E140-F139)+(E141-F140)+(F141-E141)+(E142-F141)+(F141-E141)),IF(E141<FALSE,SUM((E133-F132)+(E134-F133)+(E135-F134)+(E136-F135)+(E137-F136)+(E138-F137)+(E139-F138)+(E140-F139)+(E141-F140)+(F141-E141)+(E142-F141)),""))
GOOD Results
COL "E" COL "F" COL"G" - "J" COL"K" COL"L"
Sent Rcvd
R132 01/07/2007 12:00 PM 01/07/2007 12:02 PM 0 days 0 hours 01 minutes
6.11%
R133 01/07/2007 12:03 PM 01/07/2007 12:04 PM 0 days 0 hours 01 minutes
5.29%
R134 01/07/2007 12:05 PM 01/07/2007 12:06 PM 0 days 0 hours 01 minutes
5.05%
R135 01/07/2007 12:07 PM 01/07/2007 12:08 PM 0 days 0 hours 01 minutes
4.89%
R136 01/07/2007 12:09 PM 01/07/2007 12:10 PM 0 days 0 hours 01 minutes
5.05%
R137 01/07/2007 12:11 PM 01/07/2007 12:12 PM 0 days 0 hours 01 minutes
4.97%
.R138 01/07/2007 12:13 PM 01/07/2007 12:14 PM 0 days 0 hours 01 minutes
4.97%
R139 01/07/2007 12:15 PM 01/07/2007 12:16 PM 0 days 0 hours 01 minutes
4.89%
R140 01/07/2007 12:17 PM 01/07/2007 12:18 PM 0 days 0 hours 01 minutes
4.97%
R141 01/07/2007 12:19 PM 01/07/2007 12:20 PM 0 days 0 hours 12 minutes
60.26%
R142 01/07/2007 12:21 PM 01/07/2007 12:22 PM 0 days 0 hours 01 minutes
4.97%
R143 TOTALS: 0 days 0 hours 20 minutes
GOOD Results
Sent Rcvd
01/07/2007 12:00 PM 01/07/2007 12:02 PM 0 days 0 hours 01 minutes 6.11%
01/07/2007 12:03 PM 01/07/2007 12:04 PM 0 days 0 hours 01 minutes 5.29%
01/07/2007 12:05 PM 01/07/2007 12:06 PM 0 days 0 hours 01 minutes 5.05%
01/07/2007 12:07 PM 01/07/2007 12:08 PM 0 days 0 hours 01 minutes 4.89%
01/07/2007 12:09 PM 01/07/2007 12:10 PM 0 days 0 hours 01 minutes 5.05%
01/07/2007 12:11 PM 01/07/2007 12:12 PM 0 days 0 hours 01 minutes 4.97%
01/07/2007 12:13 PM 01/07/2007 12:14 PM 0 days 0 hours 01 minutes 4.97%
01/07/2007 12:15 PM 01/07/2007 12:16 PM 0 days 0 hours 01 minutes 4.89%
01/07/2007 12:17 PM 01/07/2007 12:18 PM 0 days 0 hours 01 minutes 4.97%
01/07/2007 12:19 PM 01/07/2007 12:20 PM 0 days 0 hours 12 minutes 60.26%
01/07/2007 12:21 PM
TOTALS: 0 days 0 hours 20 minutes
BAD Results
Sent Rcvd
01/07/2007 12:00 PM 01/07/2007 12:02 PM 0 days 0 hours 01 minutes 6.11%
01/07/2007 12:03 PM 01/07/2007 12:04 PM 0 days 0 hours 01 minutes 5.29%
01/07/2007 12:05 PM 01/07/2007 12:06 PM 0 days 0 hours 01 minutes 5.05%
01/07/2007 12:07 PM 01/07/2007 12:08 PM 0 days 0 hours 01 minutes 4.89%
01/07/2007 12:09 PM 01/07/2007 12:10 PM 0 days 0 hours 01 minutes 5.05%
01/07/2007 12:11 PM 01/07/2007 12:12 PM 0 days 0 hours 01 minutes 4.97%
01/07/2007 12:13 PM 01/07/2007 12:14 PM 0 days 0 hours 01 minutes 4.97%
01/07/2007 12:15 PM 01/07/2007 12:16 PM 0 days 0 hours 01 minutes 4.89%
01/07/2007 12:17 PM 01/07/2007 12:18 PM 0 days 0 hours 01 minutes 4.97%
01/07/2007 12:19 PM 01/07/2007 12:20 PM ######################### 60.26%
TOTALS: #########################
Convert Formula to VB for Inserting formula automatically by a command
button
Range("K" & RowID + 9).FormulaR1C1 =
"=IF(RC[-5]<FALSE,(RC[-6]-R[-1]C[-5])+(R[-1]C[-6]-R[-2]C[-5])+(R[-2]C[-6]-R[-3]C[-5])+(R[-3]C[-6]-R[-4]C[-5])+(R[-4]C[-6]-R[-5]C[-5])+(R[-5]C[-6]-R[-6]-C[-5])+(R[-6]C[-6]-R[-7]C[-5])+(R[-7]C[-6]-R[-8]C[-5])+(R[-8]C[-6]-R[-9]C[-5])+(RC[-5]-RC[-6]),""""))"
HORRIBLE Results!!
01/07/2007 12:00 PM 01/07/2007 12:02 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:03 PM 01/07/2007 12:04 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:05 PM 01/07/2007 12:06 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:07 PM 01/07/2007 12:08 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:09 PM 01/07/2007 12:10 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:11 PM 01/07/2007 12:12 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:13 PM 01/07/2007 12:14 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:15 PM 01/07/2007 12:16 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:17 PM 01/07/2007 12:18 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:19 PM 01/07/2007 12:20 PM 7 days 11 hours 56 minutes 100.00%
01/07/2007 12:21 PM 01/07/2007 12:22 PM 0 days 0 hours 01 minutes 0.00%
TOTALS: 7 days 12 hours 05 minutes
But No "######" Signs when a couple of lines are missing.
0 days 0 hours 01 minutes 0.00%
01/07/2007 12:00 PM 01/07/2007 12:02 PM
One 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:05 PM 01/07/2007 12:06 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:07 PM 01/07/2007 12:08 PM 0 days 0 hours 01 minutes 0.00%
01/07/2007 12:09 PM 01/07/2007 12:10 PM
0 days 0 hours 01 minutes 0.00%
01/07/2007 12:13 PM 01/07/2007 12:14 PM
0 days 0 hours 01 minutes 0.00%
01/07/2007 12:17 PM 01/07/2007 12:18 PM 7 days 11 hours 59 minutes 100.00%
01/07/2007 12:19 PM 01/07/2007 12:20 PM
TOTALS: 7 days 12 hours 05 minutes
NOT GOOD Either
0 days 0 hours 01 minutes #VALUE!
01/07/2007 12:00 PM 01/07/2007 12:02 PM 0 days 0 hours 01 minutes #VALUE!
01/07/2007 12:03 PM 01/07/2007 12:04 PM
0 days 0 hours 01 minutes #VALUE!
01/07/2007 12:09 PM 01/07/2007 12:10 PM
0 days 0 hours 01 minutes #VALUE!
01/07/2007 12:17 PM 01/07/2007 12:18 PM #VALUE! #VALUE!
01/07/2007 12:19 PM 01/07/2007 12:20 PM
TOTALS: #VALUE!
Can someone please tell me how I can fix this because it's starting to
drive
me crazy?
Thank You So Very Much In Advance!
Cheers,
Rob
P.S.
Sorry for the information overload.