Hello Excellers and welcome back to my #Excel #FormulaFriday blog post in my Excel 2020 series.   Today’s formula calculates how far you are from a goal.  This is sometimes referred to as a percentage of target or forecast. It is easy with an Excel formula.  The math in the formula is simple to divide the goal or target by the actual.

So, it doesn’t matter if you need to calculate how far you are away from your travel destination or near your sales target. The formula is the same.

Read All Formula Friday Blog Posts.

Calculate % Of Target.

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 performing against individual targets. Secondly, then as a whole sales team together against their common goal of €852,000.

The formula here shows the division of K9 (Actual sales to date) vs L9 (Target Sales).  Regionally the North Region is nearest its goal with 83% of target met. The West Region is sitting in the last portion with only 22 per cent of target met.

Another Example.

Here is a fun example. It is inspired by the small map in the back of the seat in front of you when travelling on an aeroplane.  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. It is the same calculation, the percentage of target.

Finally, let’s 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 journey miles.

2879 miles have been travelled so far in this example. This value is divided by the total number of miles to Newark. This gives 90% of the distance travelled or 90% of the goal reached.

Not long until landing. Tray tables stowed and seatbacks up ladies and gentlemen!.

So, this is the same simple calculation used before. It can be used in any situation when looking to show the percentage a goal or target met.

Finally, Check Out My YouTube Channel.

So, if you want more Excel and VBA tips then sign up for my Monthly Newsletter. I share 3 Tips on the first Wednesday of the month. You will receive my free Ebook