How do I calculate variance between estimate and actual in Jira?

Variance equals time spent minus the ORIGINAL estimate, in hours. Do not use the current or remaining estimate, because those fields are edited during the sprint and a re-estimated ticket will show a variance of zero however long it took.

What is the difference between original estimate and remaining estimate in Jira?

Original estimate is what was committed before work started. Remaining estimate is what a developer believes is left right now, and it moves every day. Only the original supports a variance, because only it is fixed at the point of the promise.

Why does my Jira variance report show zero for tickets that clearly overran?

Because the estimate was updated mid-flight. If the original estimate field was overwritten rather than the remaining estimate, the promise has been rewritten to match the outcome and the overrun is gone from the data.

Should I measure variance in hours or in story points?

Hours for cost questions, points for capacity planning. Never mix them in one column: points are a relative scale with no fixed size, so summing points and hours together produces a number with no unit.

← All Kits · Excel Kit

Estimate vs Actual in Jira: Turning the Export Into a Variance You Can Defend

Michael Nocito · Updated August 2026 · Every number on this page was worked before it was published

Somebody asks how far the team ran over. Jira exports a CSV with four time fields on every ticket, and picking the wrong two turns a 40 percent overrun into a clean sprint. This page is about the choice, not the export.

What you do: variance is time spent - original estimate, in hours, on completed tickets only, with re-estimated tickets flagged rather than dropped. Below is a real-shaped sprint where each of the three candidate formulas gives a different answer.

The short version. Only the original estimate can carry a variance, because it is the only field that was fixed before the work happened.

The four fields, and what each one really is

Jira field	Export column	What it is	Use for variance?
Original estimate	`timeoriginalestimate`	The commitment made before work started	Yes. This is the baseline.
Remaining estimate	`timeestimate`	Today's belief about what is left	No. It moves daily and ends at 0.
Time spent	`timespent`	Logged work, summed from worklogs	Yes. This is the actual.
Story points	custom field	Relative size, no unit	Separate report. Never in the same column as hours.

All three time fields export in seconds. Divide by 3,600 before anything else, and do it in its own column so the raw value is still there when a number looks wrong. A "1,800 hour" ticket is half an hour that nobody divided.

One sprint, three answers

Eight completed tickets. Estimate and spent are in hours.

Ticket	Original est	Time spent	Variance	Variance %	Note
PAY-101	8	10.5	+2.5	+31%
PAY-102	16	14	-2.0	-13%
PAY-103	4	13	+9.0	+225%	Scope grew
PAY-104	8	8	0	0%	Re-estimated on day 3
PAY-105	24	21.5	-2.5	-10%
PAY-106	2	6	+4.0	+200%
PAY-107	0	7.5	+7.5	no percentage	Never estimated
PAY-108	12	11	-1.0	-8%
Total	74	91.5	+17.5	+24%

The sprint ran 17.5 hours over 74 estimated, which is 24 percent. Now the three ways that number gets reported wrong:

Average of the ticket percentages gives about +61 percent, because PAY-103 and PAY-106 are tiny tickets with huge ratios. Averaging percentages weights a two hour ticket the same as a twenty four hour one. Sum the hours, then divide once.
Using the current estimate drops PAY-104's overrun to zero and shows a smaller overrun than the one that happened, because the promise was quietly rewritten to match the result.
Dropping the unestimated ticket loses 7.5 real hours, and unestimated work is usually the interesting finding, not a data quality nuisance.

PAY-107 is the row worth a sentence in the report. Seven and a half hours of work that was never planned. A variance report that silently excludes zero-estimate tickets is reporting on the planned work only, and calling it the sprint.

The re-estimate problem, and how to detect it

Jira keeps a changelog. If your export tool can carry it, pull Issue history for the original estimate field, and flag any ticket where it changed after the sprint start date. Those tickets cannot support a variance, and they should appear in the report as a count, not be quietly dropped:

Completed tickets              8
  with an untouched estimate   7
  re-estimated in flight       1   (PAY-104)
  never estimated              1   (PAY-107)

If the changelog is not available in your export, you can still say the honest thing: state that re-estimates cannot be detected in this data, which is a limitation worth writing down rather than a reason to stay quiet. That habit is the subject of documenting data limitations.

The export, and the two columns to build first

From Jira: Issues, filter to the sprint, Export > CSV (all fields). Then in Excel, before any pivot:

Est_h    =IF([@timeoriginalestimate]="",0,[@timeoriginalestimate]/3600)
Spent_h  =IF([@timespent]="",0,[@timespent]/3600)
Var_h    =[@Spent_h]-[@Est_h]
Var_pct  =IF([@Est_h]=0,"",[@Var_h]/[@Est_h])
Flag     =IF([@Est_h]=0,"unestimated",IF(ABS([@Var_h])>=4,"review",""))

The blank check matters: an unlogged ticket exports as an empty cell, not a zero, and a subtraction against an empty cell silently treats it as zero anyway. Make that decision explicit so the next person can see it was a decision.

What to report, and in which order

The total: 91.5 hours spent against 74 estimated, 24 percent over.
The concentration: two tickets, PAY-103 and PAY-106, account for 13 of the 17.5 hours.
The pattern: both were estimated at four hours or less. Small tickets are where estimation is worst, because nobody breaks down work they think is small.
The coverage: one ticket carried no estimate at all and took 7.5 hours.

That order works because it goes from the number they asked for to the action they can take. A list of eight ticket variances is data. "Our small tickets are the ones we misjudge" is a finding.

How to apply this to your own work

Export one closed sprint and check which estimate field your report is currently reading. If it is timeestimate, your overruns have been vanishing.
Divide the seconds in a named column, and keep the raw seconds column next to it.
Count the zero-estimate tickets before you filter anything. That count is a finding on its own.
Sum hours before dividing. Never average a column of percentages.
Sort by absolute hours, not by percent, and see which tickets actually moved the sprint.

The one habit to keep

Ask which field was editable while the work was happening. Anything that could be edited after the promise was made cannot measure whether the promise was kept. That question generalises well past Jira: it is the same question to ask of a forecast that gets restated, or a target that moves in July.

In your last sprint report, how many tickets carried no estimate at all?

Every number here was worked before it was published. 74 estimated, 91.5 spent, 17.5 over, 23.6 percent. Averaging the seven per-ticket percentages instead gives 60.7 percent, which is the same sprint measured wrongly.

The overrun is rarely hidden in the numbers. It is hidden in which field somebody updated.

Thinking Like an Analyst is about the part no tool does for you: framing the question, choosing the measure, saying what the data cannot support, and defending a number in a room.

Thinking Like an Analyst, $19 →

The reporting skill here is choosing the measure, not exporting it.

Defining metrics is the companion read, and operations analytics covers throughput and cycle time on the same kind of export. The Excel Kit has the pivot and lookup mechanics.

Read Defining Metrics →