Compare estimate versus actual figure

I have two columns of figures and looking to see how best to calculate out estimate accuracy. Playing around with percentages but obviously when actual higher than estimate I am returning over 100% which I don’t want!

Anyone got a Eureka moment for me on this dull grey Monday afternoon in Edinburgh!?

Thanks

Hi SuzanneG

Please can you post a sample workbook so that I can see what you've got so far.

If it makes you feel any better, it's a dull grey day here in Belfast too!!!

Regards
Stephen.

Its just two columns of figures, one column housing estimate figure & other column housing actual figure
Estimate Actual
£7,000.00 £10,700.00
£30,000.00 £20,761.30
£5,000.00 £8,450.00
£9,082.57 £16,680.57


Just was wanting to know best way to show accuracy and/or get some ideas!
thx

Hi SuzanneG

I've attached a sample workbook, but it's difficult to know how you want to display the figures. For example, if the Actual amount exceeds the Estimate amount, is this a good thing or a bad thing?

What I've done in the calculation is to force a '100%' value where it would normally calculate at over 100%. Although, I think this looks totally wrong where the two figures are different.

This may be all that you require, let me know.

Regards
Stephen.

Book1.xls

Hi SuzanneG,

Is this something you were looking for? Column C uses the following code to calculate the percentage change from the estimated amount:

=(B2-A2)/A2

Thanks,

Hey guys, thanks for your replies and are drafting out some reports to see how they all look.
cheers

Suz