Calculate EXACT number to achieve an EXACT percentage

glotgering

New Member
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
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
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
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
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
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
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.
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top