Estimates vs actuals with weighting ?

[smudgester]

Hi all,

I realise this might actually be more of a maths question but I figured some Excel guru has probably had the same problem before

So I have two columns in a spreadsheet; estimate and actual. Both columns refer to the amount of man hours a project takes. We then have a formula cell that compares them as a simple percentage - actual/estimate. So, for example:

Estimate	Actual	Delta
1	4	400%
1	2	200%
10	20	200%

All fine so far. However, as these are man hours (which have cost/planning implications), being 10 hours over on 10 hour estimate is worse than being 1 hour over on a 1 hour estimate and we would like this shown in the Excel table.

To my Friday afternoon mind, this just need a simple scaling factor or something like ( actual - estimate ) / estimate. But in the two examples, above, this still comes back as the same answer for both rows.

Can anyone help me with what I am missing here ?

Regards,

Adam

 

[offthelip]

why not use Actual - Estimate that is the real cost of all your overruns , you can then sum them for all the estimates

 

[smudgester]

why not use Actual - Estimate that is the real cost of all your overruns , you can then sum them for all the estimates

Because I don't need the sum of the overruns ( well I do but I have that already ? ), I need to know which individual rows are overrunning proportionally worse than the others.

Although, looking at it again, maybe my mistake is I should be just at be doing delta / actual ?