Calculate EXACT number to achieve an EXACT percentage

glotgering

New Member
Joined
Oct 15, 2019
Messages
6
I know the exact % I need to achieve. In this example column G2 is the target % of 67.201%.

G2 = 67.201% (This is the target %)
D4 = 4,250 (This is the number I need to increase to meet the percentage. The amount I need it to be is 4,681 for a difference of 431)

As the percentage of G2 Changes I need to calculate the EXACT number to increase D4 to meet the percentage. I have looked at several examples but all are more complicated than I need and I cannot figure it out.

Any help is greatly appreciated.

Thank you all very much.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,787
Welcome to the forum.

When you calculate a percentage, you are essentially comparing 2 numbers. You only provided one number, 4,250. However, 4,681 is 67.201% of 6,965.67. So apparently, 6,965.67 is your target number, and you are looking for a formula to raise 4250 up to 67.201% of 6,965.67. That formula is quite easy, see below.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">67.201%</td><td style="text-align: right;;"></td><td style="text-align: right;;">6965.67</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">4250</td><td style="text-align: right;;">431</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E4</th><td style="text-align:left">=I2*G2-D4</td></tr></tbody></table></td></tr></table><br />
 

glotgering

New Member
Joined
Oct 15, 2019
Messages
6
Welcome to the forum.

When you calculate a percentage, you are essentially comparing 2 numbers. You only provided one number, 4,250. However, 4,681 is 67.201% of 6,965.67. So apparently, 6,965.67 is your target number, and you are looking for a formula to raise 4250 up to 67.201% of 6,965.67. That formula is quite easy, see below.

DEFGHI
267.201%6965.67
3
44250431

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3

Worksheet Formulas
CellFormula
E4=I2*G2-D4

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
OK, Thank you Eric...I was hoping this was simple but apparently it is more complicated than I thought. You are correct that I am comparing two sets of numbers but the problem is they have very different weights.

The first series is in millions while the second series is in thousands. I will try to give you the right information. I will PayPal you a 12 pack if you can figure this out - lol

OK

The million series consists of 3 cells each and I am trying to compare the difference with the thousand series

Million SeriesThousand Series
Current21,034,088Current4,250
Spent360,575,295Spent175,252
Remaining207,288,100Remaining92,503
% Current67.201%% Current67.040%
<colgroup><col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;"> <col width="64" style="width: 48pt;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;"> <tbody> </tbody>


If I manually change the value of 4,250 in the thousand series the percentages match exactly.

Million SeriesThousand Series
Current21,034,088Current4,681
Spent360,575,295Spent175,252
Remaining207,288,100Remaining92,503
% Current67.201%% Current67.201%
<colgroup><col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;"> <col width="64" style="width: 48pt;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;"> <tbody> </tbody>

I would like know the amount needed for current in the Thousand series. In this case it is 431

I hope this helps...again a twelve pack on me
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,787
What is the formula you use to calculate the percentages?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,787
I wish you'd mentioned the M4 and O4 values earlier. It is impossible to figure out the percentage without knowing them. I tried to recreate your spreadsheet as best I could from your example and formulas. I then used Goal Seek to figure out the values for M4 and O4. Once I got that, then the formula to figure out the difference you need in I2 is easy. You can see it in J2. It's doesn't exactly match your 431 value, but that could be due to rounding errors and slight differences in the M4 and O4 values I derived.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Current</td><td style="text-align: right;;">21,034,088</td><td style=";">Current</td><td style="text-align: right;;">4,250</td><td style="text-align: right;;">429.3885</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Spent</td><td style="text-align: right;;">360,575,295</td><td style=";">Spent</td><td style="text-align: right;;">175,252</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">132473200</td><td style="text-align: right;;"></td><td style="text-align: right;;">92500</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Remaining</td><td style="text-align: right;;">207,288,100</td><td style=";">Remaining</td><td style="text-align: right;;">92,503</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">% Current</td><td style="text-align: right;;">67.201%</td><td style=";">% Current</td><td style="text-align: right;;">67.04%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I8</th><td style="text-align:left">=(<font color="Blue">I2+I4</font>)/(<font color="Blue">I4+O4</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J2</th><td style="text-align:left">=G8*(<font color="Blue">I4+O4</font>)-I4-I2</td></tr></tbody></table></td></tr></table><br />
 

glotgering

New Member
Joined
Oct 15, 2019
Messages
6
I wish you'd mentioned the M4 and O4 values earlier. It is impossible to figure out the percentage without knowing them. I tried to recreate your spreadsheet as best I could from your example and formulas. I then used Goal Seek to figure out the values for M4 and O4. Once I got that, then the formula to figure out the difference you need in I2 is easy. You can see it in J2. It's doesn't exactly match your 431 value, but that could be due to rounding errors and slight differences in the M4 and O4 values I derived.

FGHIJKLMNO
2Current21,034,088Current4,250429.3885
3
4Spent360,575,295Spent175,25213247320092500
5
6Remaining207,288,100Remaining92,503
7
8% Current67.201%% Current67.04%

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
I8=(I2+I4)/(I4+O4)
J2=G8*(I4+O4)-I4-I2

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
WOW!!! You did IT!!! 12 pack on me...(if you are old enough). Thank you so much! Can I please send you a donation on Paypal?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,787
I'm glad I could help out! :cool:

And I'm more than old enough to drink! (y)

And while I appreciate the offer of a donation, I'd prefer you just pay it forward. Help out a stranger, or give an equivalent donation to the Cancer Society. Have a great weekend!
 

glotgering

New Member
Joined
Oct 15, 2019
Messages
6
I am actually quite good with excel but just had a rough time with this formula. May I ask you how did you figure it out? I really do appreciate it.
 

Forum statistics

Threads
1,077,784
Messages
5,336,332
Members
399,076
Latest member
vullistax

Some videos you may like

This Week's Hot Topics

Top