We help IT Professionals succeed at work.

Excel + % to Goal when number is negative

165 Views
Last Modified: 2017-04-22
I have a goal of 5% and am trying to get a percent to goal, above 5% is good, and there might be scores of -42.54% and other negative numbers, while not good, I am trying to create a formula that shows me % to goal when the user has a score lower than 0% and also includes if over 0% - how would I do this ?
Comment
Watch Question

gowflowPartner
CERTIFIED EXPERT

Commented:
can you post a sample workbook with the figures and what you expect the formula results to be then we can guide you better
gowflow
CERTIFIED EXPERT

Commented:
I'd think you'd just use the same formula you'd use with a positive number (amount achieved/goal). So, if for example, your goal was 50 and the amount achieved was -25, the % of goal would be -50%.

Author

Commented:
I dont want it to be a negative percentage though - so if goal was 50% and i was -25%, (not actual calculation) but i want percent to goal to be something like 25%
CERTIFIED EXPERT

Commented:
Ah, you'll have to give some precise rules then in order for us to give you a formula, because it wouldn't really be 25%.

Author

Commented:
i know it wouldnt be 25% just using that as an example

The range can be from -100% to 100%, -100% being the worst and 100% being the best, the goal being 5% as negative percentages are expected, so tyring to figure with this scale how to guage percent to goal without having huge variance and negative % to goals.
CERTIFIED EXPERT

Commented:
so if it's 5% you want to say 100% to goal?? What would give the result of 0%?

Author

Commented:
correct if score is 5% it would be 100% to goal
CERTIFIED EXPERT

Commented:
What's zero percent? Is there a calculated percent below zero where you want it to always say 0% if it's at that level or below?
gowflowPartner
CERTIFIED EXPERT

Commented:
yoooouuuu hoooouuuu   ....

can you paste some data ?
gowflow

Author

Commented:
0% to goal would be -100%
gowflowPartner
CERTIFIED EXPERT

Commented:
you talk or a score in percent. What are the constituants  to get the percent ? like 25% of what ? if we get the figures then maybe it could be easier to grasp what you want.

I would appreciate being answered !!!
gowflow

Author

Commented:
Here are some numbers

Goal: 5%

Actuals: -31.94%, -28.70%, -19.98%

Worst someone can get is -100%, best is 100%
gowflowPartner
CERTIFIED EXPERT

Commented:
ok give me what you expect the formula to give you for
Goal: 5%

 Actuals: -31.94%, -28.70%, -19.98%
and then we can build the formula

or am I getting something wrong ?
gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
Is this what you want ?
check the file

gowflow
PercToGoal.xlsx
CERTIFIED EXPERT

Commented:
(corrected to actually use B4. I copied the formula from a different row in my test spreadsheet.)

I think this does it. It certainly appears to work anyway.

This will give 100% as the maximum and 0% as the minimum.


If B4 is the number reached, and E4 is the goal:

=IF((B4+$E$2)/105>1,1,IF((B4+$E$2)/105<0,0,(B4+$E$2)/105))

Author

Commented:
when i put -31.94% into B4 and 5% into E2 i get 0.00% for this formula as the % to Goal - 0% should only be if -100 is in B4
CERTIFIED EXPERT

Commented:
B4 should be the number reached not the percent. The goal (E2)l should be the total goal (of which 5% is 100% in your case).
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you for your help
gowflowPartner
CERTIFIED EXPERT

Commented:
@Missus Miss_Sellaneus
Your in pro in understanding what was requested !!! :)
gowflow
CERTIFIED EXPERT

Commented:
Wait, that's not right. I forgot to check for goals other than 100!!

Here's the corrected version:goals.xlsx

=IF((B4+$E$2)/105*100/$E$2>1,1,IF((B4+$E$2)/105*100/$E$2<0,0,(B4+$E$2)/105*100/$E$2))

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.