Excel Tip – Are We There Yet? Are We There? Yet How Close To Your Goal Are You? Using Excel

This article will show you how a simple formula can calculate how far we are away from a goal. This can be applied in many situations and is sometimes referred to as percentage of target or forecast. The math in the formula is simple just divide the goal or target by the actual.

So, it doesn’t matter if it’s calculating how far you are away from your travel destination or near you are to your sales target, the formula is the same- to work out how far you are away from your goal?.

Let’s take some examples.

We have a list of regional sales areas, and their Target Sales and Actual Sales values to date. By simply dividing the actual sales by the target we calculate how individually the regional areas are doing against their individual targets then as a whole sales team together against their common goal of €852000.

Our data in an Excel work book as below”

Region – Target Sales – Actual Sales To Date – % From Goal

North – 150,000 – 125,000 – 83%

South – 325,000 – 120,000 – 37%

East – 127,000 – 29,000 – 23%

West – 250,000 – 56,000 – 22%

Total – 852,000 – 330,000 – =L9/K9

Target sales are in column K and Actual Sales to Date in Column L

The formula here shows the division of K9(Actual sales to to date) vs L9(Target Sales). We can see that regionally the North Region is nearest it’s goal with 83% of target met and furthest from their target only achieving 22% of their target to date is the West Region.

Here is a fun example… inspired by the small map in the back of the seat in front of you when travelling on an airplane. You know the one where it tells you altitude, ground speed, airspeed, time to destination and outside temperature (which is always scary!). I always try to gauge how much of the journey I have completed and how much is to go, always hoping if I am bored on the flight that I have travelled at least 50% of the journey.

So we can apply the same formula to work out how much of the journey we have completed. The goal of the journey is the total number of miles travelled the journey is and the actual being how many miles have been travelled at the point you are at.

We have travelled 2879 miles so far in this example… so this divided by the total number of miles to Newark gives us 90% of the distance travelled or 90% of our goal reached… not long until landing… tray tables stowed and seat backs up ladies and gentlemen!…

OK there we go, the same simple calculation to show how far we are away from a goal or sometimes knows as percentage of target or forecast, which can be applied in many situations.