Calculate EXACT number to achieve an EXACT percentage

G

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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
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.


Book1
DEFGHI
267.201%6965.67
3
44250431
Sheet3
Cell Formulas
RangeFormula
E4=I2*G2-D4
 
Upvote 0
Eric W said:
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

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]67.201%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6965.67[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4250[/TD]
[TD="align: right"]431[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E4[/TH]
[TD="align: left"]=I2*G2-D4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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

[TABLE="width: 276"]
<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>[TR]
[TD="width: 151, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , colspan: 2"]Million Series[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 151, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , colspan: 2"]Thousand Series[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Current[/TD]
[TD="bgcolor: transparent, align: right"]21,034,088[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Current[/TD]
[TD="bgcolor: transparent, align: right"]4,250[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Spent[/TD]
[TD="bgcolor: transparent, align: right"]360,575,295[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Spent[/TD]
[TD="bgcolor: transparent, align: right"]175,252[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Remaining[/TD]
[TD="bgcolor: transparent, align: right"]207,288,100[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Remaining[/TD]
[TD="bgcolor: transparent, align: right"]92,503[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]% Current[/TD]
[TD="bgcolor: transparent, align: right"]67.201%[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]% Current[/TD]
[TD="bgcolor: transparent, align: right"]67.040%[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 276"]
<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>[TR]
[TD="width: 151, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , colspan: 2"]Million Series[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 151, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , colspan: 2"]Thousand Series[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Current[/TD]
[TD="bgcolor: transparent, align: right"]21,034,088[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Current[/TD]
[TD="bgcolor: transparent, align: right"]4,681[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Spent[/TD]
[TD="bgcolor: transparent, align: right"]360,575,295[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Spent[/TD]
[TD="bgcolor: transparent, align: right"]175,252[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Remaining[/TD]
[TD="bgcolor: transparent, align: right"]207,288,100[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Remaining[/TD]
[TD="bgcolor: transparent, align: right"]92,503[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]% Current[/TD]
[TD="bgcolor: transparent, align: right"]67.201%[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]% Current[/TD]
[TD="bgcolor: transparent, align: right"]67.201%[/TD]
[/TR]
</tbody>[/TABLE]

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
 
Upvote 0
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.


Book1
FGHIJKLMNO
2Current21,034,088Current4,250429.3885
3
4Spent360,575,295Spent175,25213247320092500
5
6Remaining207,288,100Remaining92,503
7
8% Current67.201%% Current67.04%
Sheet1
Cell Formulas
RangeFormula
I8=(I2+I4)/(I4+O4)
J2=G8*(I4+O4)-I4-I2
 
Upvote 0
Eric W said:
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
CurrentCurrent
SpentSpent
RemainingRemaining
% Current% Current

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"]21,034,088[/TD]

[TD="align: right"]4,250[/TD]
[TD="align: right"]429.3885[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]360,575,295[/TD]

[TD="align: right"]175,252[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]132473200[/TD]
[TD="align: right"][/TD]
[TD="align: right"]92500[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]207,288,100[/TD]

[TD="align: right"]92,503[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]67.201%[/TD]

[TD="align: right"]67.04%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I8[/TH]
[TD="align: left"]=(I2+I4)/(I4+O4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]=G8*(I4+O4)-I4-I2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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?
 
Upvote 0
I'm glad I could help out! :cool:

And I'm more than old enough to drink! :beerchug:

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!
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Show Error message If result not within 5% of figure in seperate table
Replies
2
Views
325
Woodpusher147
W
A
  • Question Question
Finding the average percentage from countif of achievement perecentages
Replies
2
Views
604
anuradhagrewal
A
L
  • Question Question
Formula for a sale price which has circular references
2 3
Replies
27
Views
2K
felixstraube
F
J
  • Question Question
Water Bill Calculator w/ROI from Percentage Savings
Replies
1
Views
423
Jared016
J
N
  • Solved
Formula to find the in-between number in a range
Replies
2
Views
493
Fluff
Fluff

Forum statistics

Threads
1,223,706
Messages
6,173,998
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top