SOLVED

How to display actual of 133% when target is 100% - i.e Actual is 33% too high

%3CLINGO-SUB%20id%3D%22lingo-sub-1419992%22%20slang%3D%22en-US%22%3EHow%20to%20display%20actual%20of%20133%25%20when%20target%20is%20100%25%20-%20i.e%20Actual%20is%2033%25%20too%20high%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1419992%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%20this%20is%20advice%20more%20than%20an%20excel%20formula%2C%20but%20wanted%20ideas%20of%20how%20others%20would%20display%20an%20actual%20of%20133%25%20when%20the%20target%20is%20100%25%20-%20i.e%20we%20should%20never%20go%20over%20100%25%2C%20so%20the%20actual%20tracking%2033%25%20too%20high.%20For%20context%2C%20we%20have%20a%20list%20of%20KPI's%20where%20we%20are%20showing%20'Progress%20to%20Target'%20for%20many%20parts%20of%20the%20business%20and%20in%20most%20cases%2C%20its%20good%20to%20be%20over%20100%25%2C%20but%20this%20particular%20one%2C%20it%20is%20not.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJenny%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1419992%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1422341%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20display%20actual%20of%20133%25%20when%20target%20is%20100%25%20-%20i.e%20Actual%20is%2033%25%20too%20high%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1422341%22%20slang%3D%22en-US%22%3EIt's%20really%20important%20to%20be%20sure%20the%20data%20collected%20is%20ok.%20Meanwhile%20you%20need%20to%20review%20if%20there%20is%20no%20typo%20or%20duplicate%20values%20that%20increases%20the%20total%20value%20and%20by%20consequence%20the%20final%20percentage.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1423319%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20display%20actual%20of%20133%25%20when%20target%20is%20100%25%20-%20i.e%20Actual%20is%2033%25%20too%20high%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1423319%22%20slang%3D%22en-US%22%3Ethe%20133%25%20is%20correct%2C%20it%20is%20cost%20per%20lead.%20We%20want%20to%20show%20that%20it%20is%2033%25%20too%20high%20as%20the%20target%20is%20100%25.%20Thanks%20for%20help.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1423406%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20display%20actual%20of%20133%25%20when%20target%20is%20100%25%20-%20i.e%20Actual%20is%2033%25%20too%20high%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1423406%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668941%22%20target%3D%22_blank%22%3E%40JennySommet%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDepends%20on%20concrete%20goals%20and%20data%20structuring.%20You%20may%20apply%20conditional%20formatting%20rule%20which%20highlight%20cell(s)%20if%20the%20actual%20is%20more%20than%20target%20with%20some%20gap.%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20data%20validation%20which%20won't%20allow%20to%20add%20actual%20if%20it's%20more%20than%20target%20with%20gap.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hi everyone,

 

Perhaps this is advice more than an excel formula, but wanted ideas of how others would display an actual of 133% when the target is 100% - i.e we should never go over 100%, so the actual tracking 33% too high. For context, we have a list of KPI's where we are showing 'Progress to Target' for many parts of the business and in most cases, its good to be over 100%, but this particular one, it is not.

 

Thanks!

 

Jenny

3 Replies
Highlighted
It's really important to be sure the data collected is ok. Meanwhile you need to review if there is no typo or duplicate values that increases the total value and by consequence the final percentage.
Highlighted
the 133% is correct, it is cost per lead. We want to show that it is 33% too high as the target is 100%. Thanks for help.
Highlighted
Best Response confirmed by JennySommet (Contributor)
Solution

@JennySommet 

Depends on concrete goals and data structuring. You may apply conditional formatting rule which highlight cell(s) if the actual is more than target with some gap.

That could be data validation which won't allow to add actual if it's more than target with gap.