What is the formula to show the negative value during computing the time variance?

[Vinosh]

Case is this:

Each task is estimated certain number of hours to complete. The actual number of hours consumed to complete the task is calculated.

Now I want to find out the variance between estimated time and actual time. I get #### when variance is negative value.

[TABLE="width: 225"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]Estimate[/TD]
[TD]Actual[/TD]
[TD]Variance +/-[/TD]
[/TR]
[TR]
[TD]2:00:00[/TD]
[TD]8:00:00[/TD]
[TD="align: center"]##########[/TD]
[/TR]
[TR]
[TD]4:00:00[/TD]
[TD]8:30:00[/TD]
[TD="align: center"]##########[/TD]
[/TR]
[TR]
[TD]8:00:00[/TD]
[TD]6:00:00[/TD]
[TD]2:00:00[/TD]
[/TR]
[TR]
[TD]16:00:00[/TD]
[TD]3:00:00[/TD]
[TD]13:00:00[/TD]
[/TR]
[TR]
[TD]32:00:00[/TD]
[TD]1:00:00[/TD]
[TD]31:00:00[/TD]
[/TR]
</tbody>[/TABLE]

What is the formula to calculate variance and to show the negative value in hours?

 

[Caribeiro77]

If you don't need the variance column to future calculations you can use:

=IF(B1>A1,B1-A1,TEXT(MAX(A1:B1)-MIN(A1:B1),"-h:mm:ss"))

 

[Peter_SSs]

If you don't need the variance column to future calculations you can use:

=IF(B1>A1,B1-A1,TEXT(MAX(A1:B1)-MIN(A1:B1),"-h:mm:ss"))

That will show all variances as negative, not just the first two in the sample data.

Instead, this slight variation:

Excel Workbook
	A	B	C
1	Estimate	Actual	Variance +/-
2	2:00:00	8:00:00	-6:00:00
3	4:00:00	8:30:00	-4:30:00
4	8:00:00	6:00:00	2:00:00
5	16:00:00	3:00:00	13:00:00
6	32:00:00	1:00:00	7:00:00
Variance

Another option is to change Excel's default date system for this workbook to the 1904 date system and negative time should display correctly with a simple subtraction:

Excel Workbook
	A	B	C
1	Estimate	Actual	Variance +/-
2	2:00:00	8:00:00	-6:00:00
3	4:00:00	8:30:00	-4:30:00
4	8:00:00	6:00:00	2:00:00
5	16:00:00	3:00:00	13:00:00
6	32:00:00	1:00:00	7:00:00
Variance (2)

 

[Vinosh]

That resolves my issue.
What is the problem with this formula if I have to use the result of variance in future calculations?

In fact, I have about 3000+ such odd tasks for which I need to find the variance between estimated time and actual consumed time. I will have to validate the difference on a regular period, say weekly/fortnightly etc, to take action on the variance, so that the work progresses smoothly.

Will I have a any issue in this if use the given formula?

 

[Peter_SSs]

Firstly, notice that the result in row 6 of each of my previous screen shots appears incorrect.
In the first one, the TEXT formula needs adjustment - see corrected formula in the first screen shot below.
In the second one, the cells need to have a Custom format applied: [h]:mm:ss so that the results appear as shown in the second screen shot below.

What is the problem with this formula if I have to use the result of variance in future calculations?

Well, the problem is if you want to do some further arithmetic calculations with these results. Since the =TEXT.. formula does not return numbers, you will have a problem.

For example, suppose that you want to find the total (SUM) of all the variances, or the average variance, look what happens in cells C7 and C8 below. Neither formula gives the correct results.

Excel Workbook
	A	B	C
1	Estimate	Actual	Variance +/-
2	2:00:00	8:00:00	-6:00:00
3	4:00:00	8:30:00	-4:30:00
4	8:00:00	6:00:00	2:00:00
5	16:00:00	3:00:00	13:00:00
6	32:00:00	1:00:00	31:00:00
7		Total	0:00:00
8		Average	#DIV/0!
Variance

However, with the change in Date system method, these additional calculations work as expected.

Excel Workbook
	A	B	C
1	Estimate	Actual	Variance +/-
2	2:00:00	8:00:00	-6:00:00
3	4:00:00	8:30:00	-4:30:00
4	8:00:00	6:00:00	2:00:00
5	16:00:00	3:00:00	13:00:00
6	32:00:00	1:00:00	31:00:00
7		Total	35:30:00
8		Average	7:06:00
Variance (2)

 

[Vinosh]

That is informative. I understand the point. Thanks a lot.

 

[Peter_SSs]

That is informative. I understand the point. Thanks a lot.

No problem. Glad to help.