Question description:
This user has given permission to use the problem statement for this
blog.
I am trying to write an excel formula so that if I plug a percentage in F12 it will populate the amount needed in E7.
F10=B4-E7
F11=B5-E7
F11/F10= F12
Solved by G. H. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
01/06/2018 - 01:13
Welcome, Thanks for choosing Got It Pro-Excel.
User
01/06/2018 - 01:13
hello
Excelchat Expert
01/06/2018 - 01:14
Hi, do you have sample data that we can work on?
User
01/06/2018 - 01:14
I just added the info to the document preview
Excelchat Expert
01/06/2018 - 01:15
Very good. Let me go through it please...
User
01/06/2018 - 01:15
ok thank you
User
01/06/2018 - 01:19
I updated the fields with the formulas I have
User
01/06/2018 - 01:20
Right now if I enter a value in E7 it will populate F10, F11 and F12
User
01/06/2018 - 01:20
I would like to be able to plug data into F12 and have E7 populate
Excelchat Expert
01/06/2018 - 01:21
Okay.
Excelchat Expert
01/06/2018 - 01:21
What do you want E7 to populate?
User
01/06/2018 - 01:22
It should populate a dollar amount that we will subtract fromboth F10 and F11
User
01/06/2018 - 01:23
and when it is subtracted from F10 and F11 then it should equal the % that we have in F12
Excelchat Expert
01/06/2018 - 01:27
I am already seeing E7 already has 1,300,000
User
01/06/2018 - 01:28
it is but it is manually entered and it is populating F10-12
User
01/06/2018 - 01:28
I want to be able to do the opposite.
User
01/06/2018 - 01:29
Plug in F12 and have it populate the amount needed in E7 along with the values in F10 and F11
Excelchat Expert
01/06/2018 - 01:30
Okay. What is the formula for calculating the impact needed?
Excelchat Expert
01/06/2018 - 01:30
That is the value in E7
Excelchat Expert
01/06/2018 - 01:31
I mean the manual formula.
User
01/06/2018 - 01:32
Right now we just guess the $ amount and keep manually changing it until we like the % that is in F12
User
01/06/2018 - 01:32
but it would be better to be able to say we want the percentage to be 15.5
Excelchat Expert
01/06/2018 - 01:33
Okay. How is the percentage in F12 related to the value in E7?
User
01/06/2018 - 01:35
The dollar amount in E7 is subtracted from BF to get F10 and from B5 to get F11 then F12 is the shows what percentage of F10 is made up of F11
User
01/06/2018 - 01:35
B4 not BF
User
01/06/2018 - 01:37
so its (B5-E7)/(B4-E7)=F12
Excelchat Expert
01/06/2018 - 01:37
So you basically mean, you want to adjust F12 manually but E7 should be changing automatically?
User
01/06/2018 - 01:37
yes
Excelchat Expert
01/06/2018 - 01:37
Very good. Let me formulate a workaround for it.
User
01/06/2018 - 01:38
thank you
User
01/06/2018 - 01:48
Any luck?
Excelchat Expert
01/06/2018 - 01:49
Working on it please....
User
01/06/2018 - 01:49
ok thank you
Excelchat Expert
01/06/2018 - 01:55
The best way to handle this, is not by formulas.
Excelchat Expert
01/06/2018 - 01:56
There is a data tool called "Goal Seek"
Excelchat Expert
01/06/2018 - 01:56
Have you used it before?
User
01/06/2018 - 01:57
I have not
Excelchat Expert
01/06/2018 - 01:57
Okay.
Excelchat Expert
01/06/2018 - 01:58
Let me process the steps for you on how to use it.
Excelchat Expert
01/06/2018 - 01:58
It is very simple.
User
01/06/2018 - 01:58
ok thank you
Excelchat Expert
01/06/2018 - 02:02
STEP 1
Excelchat Expert
01/06/2018 - 02:03
Open your workbook file, in your desktop Excel application with the data you posted on the online sheet.
Excelchat Expert
01/06/2018 - 02:03
Have you opened?
User
01/06/2018 - 02:04
yes
Excelchat Expert
01/06/2018 - 02:04
Very good.
Excelchat Expert
01/06/2018 - 02:04
STEP 2
Excelchat Expert
01/06/2018 - 02:04
Go to Data tab
User
01/06/2018 - 02:04
ok
Excelchat Expert
01/06/2018 - 02:05
Under Data Tools click on What-If Analysis
Excelchat Expert
01/06/2018 - 02:05
And then select Goal Seek
User
01/06/2018 - 02:05
ok
Excelchat Expert
01/06/2018 - 02:05
A Goal Seek dialog box pops up, right?
User
01/06/2018 - 02:05
yes
Excelchat Expert
01/06/2018 - 02:06
In the Set cell field we usually put the field which we want to set.
Excelchat Expert
01/06/2018 - 02:07
In this case, the value which we always want to set is the value in cell F12.
Excelchat Expert
01/06/2018 - 02:07
Right?
User
01/06/2018 - 02:07
yes
Excelchat Expert
01/06/2018 - 02:08
Therefore erase everything in it, put a cursor in it and then go and click on F12 on the sheet.
Excelchat Expert
01/06/2018 - 02:08
So that you end up with $F$12
User
01/06/2018 - 02:09
ok
Excelchat Expert
01/06/2018 - 02:09
Are we together?
User
01/06/2018 - 02:09
yes
Excelchat Expert
01/06/2018 - 02:09
I see time is running out.
Excelchat Expert
01/06/2018 - 02:09
In the To value put the value which you wish to set F12 to.
Excelchat Expert
01/06/2018 - 02:10
For example you have mentioned you want to set it to 15.5%
Excelchat Expert
01/06/2018 - 02:10
Ensure you type also the % symbol.
Excelchat Expert
01/06/2018 - 02:10
You must type "15.5%" NOT 15.5
User
01/06/2018 - 02:10
ok
Excelchat Expert
01/06/2018 - 02:10
An in the By changing cell field.
Excelchat Expert
01/06/2018 - 02:11
Type the cell which we wish to change.
Excelchat Expert
01/06/2018 - 02:11
In this case E7
Excelchat Expert
01/06/2018 - 02:11
Then press Okay
Excelchat Expert
01/06/2018 - 02:11
The values in the sheet should adjust accordingly.
Excelchat Expert
01/06/2018 - 02:12
If this does not solve the problem, kindly let us know. Thank you.
Excelchat Expert
01/06/2018 - 02:12
It was my pleasure helping you solve this problem. Any time you have a problem with Excel do not hesitate to come back for help. Enjoy the rest of your day!
User
01/06/2018 - 02:12
thank you!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
