Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.