<div>
can you post a sample workbook with the figures and what you expect the formula results to be then we can guide you better<br />
gowflow
</div>


<div>
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%.
</div>


<div>
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%
</div>


<div>
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%.
</div>


<div>
i know it wouldnt be 25% just using that as an example<br />
<br />
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.
</div>


<div>
so if it's 5% you want to say 100% to goal?? What would give the result of 0%?
</div>


<div>
correct if score is 5% it would be 100% to goal
</div>


<div>
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?
</div>


<div>
yoooouuuu hoooouuuu &nbsp; ....<br />
<br />
can you paste some data ?<br />
gowflow
</div>


<div>
you talk or a score in percent. What are the constituants &nbsp;to get the percent ? like 25% of what ? if we get the figures then maybe it could be easier to grasp what you want.<br />
<br />
I would appreciate being answered !!!<br />
gowflow
</div>


<div>
Here are some numbers <br />
<br />
Goal: 5%<br />
<br />
Actuals: -31.94%, -28.70%, -19.98%<br />
<br />
Worst someone can get is -100%, best is 100%
</div>


<div>
ok give me what you expect the formula to give you for<br />
Goal: 5%<br />
<br />
&nbsp;Actuals: -31.94%, -28.70%, -19.98%<br />
and then we can build the formula<br />
<br />
or am I getting something wrong ?<br />
gowflow
</div>


<div>
Is this what you want ?<br />
check the file<br />
<br />
gowflow<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/04_w16/1158101/PercToGoal.xlsx" target="_blank" class="file-inline" title="perc to goal (10 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>PercToGoal.xlsx</a>
</div>


PercToGoal.xlsx

<div>
<i>(corrected to actually use B4. I copied the formula from a different row in my test spreadsheet.)</i><br />
<br />
I think this does it. It certainly appears to work anyway.<br />
<br />
This will give 100% as the maximum and 0% as the minimum.<br />
<br />
<br />
If B4 is the number reached, and E4 is the goal:<br />
<br />
=IF((B4+$E$2)/105&gt;1,1,IF((<wbr />B4+$E$2)/1<wbr />05&lt;0,0,(B4<wbr />+$E$2)/105<wbr />))
</div>


<div>
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
</div>


<div>
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).
</div>


goals.xlsx

<div>
@Missus Miss_Sellaneus<br />
Your in pro in understanding what was requested !!! :)<br />
gowflow
</div>


<div>
Wait, that's not right. I forgot to check for goals other than 100!!<br />
<br />
Here's the corrected version:<a href="https://filedb.experts-exchange.com/incoming/2017/04_w16/1158108/goals.xlsx" target="_blank" class="file-inline" title="goals (9 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>goals.xlsx</a><br />
<br />
=IF((B4+$E$2)/105*100/$E$2<wbr />&gt;1,1,IF((B<wbr />4+$E$2)/10<wbr />5*100/$E$2<wbr />&lt;0,0,(B4+$<wbr />E$2)/105*1<wbr />00/$E$2))
</div>


<div>
<div class="content wysiwyg-content">
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 ?
</div>
</div>


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 ?

Excel + % to Goal when number is negative

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.