Difference between a number and a target number
I have a column gives me a % of where the team is. I want to know the difference between that number and our Target Goal.
Example:
Column 1: 95.91%
Target Goal is always 99%
What is the difference between the two.
Once I have that I will set up some conditional formatting to turn red when we don't hit our target but for the life of me I can't get my formula to work like it did in excel.
Thanks!
Best Answer
-
It's the way the percentages are looking and I see the issue now. So, for the first column you're asking "How far away from 95% (the target) am I?". The answer is that you're over 95% by 1.5%, so since we're subtracting it says -1.5%. The second column says "How far away am I from 99%?". The answer to that is that you're 2.5% away.
Depending on how you want this to look, there are several ways you could handle this. You could say that anything that meets or exceeds the target goal is "Complete" and then show the variance on anything else. You could leave it and explain to people what it means. There are probably other options depending on how you wanted to do that.
If you wanted to assume anything at or above the target is complete you could use:
=IF(0.95 - [Average Between Both Teams]@row <=0, "Complete", 0.95 - [Average Between Both Teams]@row)
You could also potentially replace "Complete" with 1, which would mark it 100% complete.
Answers
-
=0.99 - [Column 1]@row
That should give you the difference.
-
Thank you! One weird thing though:
Here are the formula's for both of those:
Why would I be getting a minus/positive answer difference?
Thanks again for all your help!
-
It's the way the percentages are looking and I see the issue now. So, for the first column you're asking "How far away from 95% (the target) am I?". The answer is that you're over 95% by 1.5%, so since we're subtracting it says -1.5%. The second column says "How far away am I from 99%?". The answer to that is that you're 2.5% away.
Depending on how you want this to look, there are several ways you could handle this. You could say that anything that meets or exceeds the target goal is "Complete" and then show the variance on anything else. You could leave it and explain to people what it means. There are probably other options depending on how you wanted to do that.
If you wanted to assume anything at or above the target is complete you could use:
=IF(0.95 - [Average Between Both Teams]@row <=0, "Complete", 0.95 - [Average Between Both Teams]@row)
You could also potentially replace "Complete" with 1, which would mark it 100% complete.
-
Thanks! I really appreciate your guidance! Have a great day!
-
No problem. You too.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!