Find Value of Cell Such That Two Other Cells Are Equal

JohnTravolski

New Member
Joined
Nov 25, 2015
Messages
37
Office Version
  1. 2019
Platform
  1. Windows
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:

r/excel - Find Value of Cell Such That Two Other Cells Are Equal

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
 

Some videos you may like

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?
 
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
Joined
Nov 25, 2015
Messages
37
Office Version
  1. 2019
Platform
  1. Windows
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,119,175
Messages
5,576,544
Members
412,730
Latest member
Thundereagle
Top