Calculating time variances (-/+) when time is a negative number

eveb
eveb used Ask the Experts™
on
I have several columns to calculate the difference between estimated time and actual time (which is the sum of several columns).
E= Estimated time
F= Actual time (sum of P thru W)
G= Variance (plus or minus minutes and seconds)

This is the formula as it is now:
=IF(F38=0,"",REPT("-",E38>F38)&TEXT(ABS(E38-F38),"[m]:ss"))

When the actual time in cell F equals zero, it posts no result.  I would like it to post a negative result: I.E.  If the actual time equals zero, and the estimated time was :32, I would like the result " -:32" (negative 32 seconds) to be the result in G.

E       F       G           P  Q  R  S... W
Est.  Act.   Var.      Timings
:15   :17     :02         :10    :05    :02   (Actual is greater than estimate, result is +)
:10   :10     0            :05    :05            (Actual equals estimate, result is 0)
:20    :12    -:08        :05    :03    :04   (Actual is less than estimate, result is -)
:32    0      -:32          0                       (ACTUAL EQUALS ZERO, RESULT IS NEGATIVE)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2013

Commented:
Hello eveb,
The first IF function in that formula explicitly instructs it to return a blank if F38 is zero, so if you want a negative in that case just remove that IF, i.e. use
=REPT("-",E38>F38)&TEXT(ABS(E38-F38),"[m]:ss")
regards, barry

Author

Commented:
That almost works... the problem is: if I haven't entered an actual time yet (F=blank) then I want it to leave the variance blank. If F=0, then I want the negative number.  With this solution, it gives me a negative number for all the blanks.
Most Valuable Expert 2013
Commented:
OK then, back to your original formula but instead of F38=0 use F38="", i.e.
=IF(F38="","",REPT("-",E38>F38)&TEXT(ABS(E38-F38),"[m]:ss"))
regards, barry

Author

Commented:
column F will never be empty, since it contains =sum(P2..W2)  the sum of P thru W... but if I change the first F reference to P (which is the first column I would enter data into) then it works!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial