Find Value of Cell Such That Two Other Cells Are Equal

JohnTravolski

New Member
I have a spreadsheet with several complicated formulas and I'm wanting a cell B6 to display a value that would make two other cells equivalent (one of the cells depends on B6 and the other cell does not). I imagine it would have to be done using some numerical method, but I don't know if Excel can even do this at all. Take, for example, the following spreadsheet:

The PV Factor column has the formula

Excel Formula:
``=(1+\$B\$6)^(DATEDIF(F2,TODAY(),"d")/365)``

and the "Expected Present Value" cell has the formula

Excel Formula:
``=SUMPRODUCT(E:E,G:G)``

while the "Actual Value" column was a manually-entered constant. I'm wanting to the find the value of the "Long Term Constant Return" that makes the cells "Actual Value" and "Expected Present Value" equal. Unfortunately, sumproduct is not an invertible function, so I cannot analytically solve for the Long Term Constant Return with a formula. I need to use some kind of numerical method to find an approximate answer, but I want Excel to do this for me automatically and display the result in a cell.

How can I have Excel calculate this value for me? I have attached the spreadsheet here: solve excel equation.xlsx

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
L

Legacy 456155

Guest
Solver came up with 16.9032496047279%
Is that correct?

JohnTravolski

New Member
Solver came up with 16.9032496047279%
Is that correct?
Yes, that sounds right, but I'm not familiar with solver. Can it be set up to calculate that value automatically?

L

Legacy 456155

Guest
Hi John.
Can it be set up to calculate that value automatically?
Using Solver, I don't know. If you are open to a bit of code in your project, we could automate the calculation of "Long Term Constant Return" by responding to a change in the "Actual Value" cell. Tell me what you think.

JohnTravolski

New Member
Hi John.

Using Solver, I don't know. If you are open to a bit of code in your project, we could automate the calculation of "Long Term Constant Return" by responding to a change in the "Actual Value" cell. Tell me what you think.
Hi, I found a way to do it by calling "Goal Seek" in VBA. It still requires me to click a button in excel to run it, but it works well enough. This was the line I needed in VBA (the cells I reference here don't match my worksheet because that was just a simplified version, but it should be clear enough):
Range("C5").GoalSeek goal:=Range("E5").Value, ChangingCell:=Range("B22")

L

Replies
1
Views
135
Replies
3
Views
146
Replies
2
Views
226
Replies
2
Views
146
Replies
1
Views
96