Difference between a number and a target number

Mindy Schneider

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!

David Tutwiler

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.

David Tutwiler

=0.99 - [Column 1]@row

That should give you the difference.

Mindy Schneider

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!

David Tutwiler

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.

Mindy Schneider

Thanks! I really appreciate your guidance! Have a great day!

David Tutwiler

No problem. You too.