Calculting an average from time variance that has positive and negative numbers.

Good day,
I'm trying to get . In other words, the difference of Sched Up Time - Actual Up Time = Variance. Anything with a "+" is late and "-" is early. The problem is that excel takes those "+" and "-" as part of a formula or makes these numbers as text. I have to total up the Variance column to give an overall average for that week. Can this be done in Excel? I receive errors if I use average since it doesn't like those "+" or "-"'s.

Date Sched Up Time Actual Up Time Variance Status
6/18/2016 08:00 08:32 +00:32 Late
6/20/2016 08:00 08:31 +00:31 Late
6/21/2016 08:00 07:26 -00:34 Early
6/22/2016 08:00 08:45 +00:45 Late
6/23/2016 08:00 09:05 +01:05 Late
6/24/2016 08:00 09:46 +01:46 Late

Thanking you in advance.

Originally Posted by chelsea7A

the difference of Sched Up Time - Actual Up Time = Variance. Anything with a "+" is late and "-" is early. The problem is that excel takes those "+" and "-" as part of a formula or makes these numbers as text.

How did you get the "+" and "-" signs, in the first place?

It would be best to solve the problem at the source: the formulas that calculate column D ("variance"). Since Excel does not usually like negative time values (and enabling the 1904 date system is a bad idea, IMHO), it would be better to calculate "variance" in decimal minutes. For example:

D2: =ROUND((C2-B2)*1440, 2)

formatted as Custom +General;-General;0 . ROUND is used to eliminate anomalies that creep into binary arithmetic with decimal fractions, which is how Excel time is stored.

Then the average is simply =AVERAGE(D2:D7), with the same Custom format.

However, if the "variance" data is input as text, then array-enter the following formula (press ctrl+shift+Enter instead of just Enter):

=AVERAGE(ROUND(MID(D2:D7,2,7)*IF(LEFT(D2:D7,1)="+",1440,-1440),0))

In the Custom format, you can change General and 0 to 0.00 or any number of decimal places that you want.