dynamic calculation excel

[DODO1]

Hello,

I'm trying to calculate an value x or i which is the result of the formula x = y*(1+i).
x = Future value
y = Present value
i = intrest rate
This wasn't so hard until I wanted to calculate x or i depending on the output the user wants (i or x) and only use 2 visible input boxes (x and i). y is a given number which can not be changed by the user.

exp.:
given data: y = 1 ; i = 0.1
calculated: x = 1.1
The user can now change the value of i => need to calculate x for exp. i becomes 0.05, result of x should become 1.05
or change the value of x => need to calculate i.

thank you

DODO1

 

[johnwm]

Just do an algebraic conversion:
x = y * (1 + i) =>
x/y= 1 + i =>
i = x/y - 1

so when x changes then set i = x/y -1


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[ADoozer]

LOL johnwm you enjoyin your maths lesson today?? LOL

If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
A General Guide To Excel in VB FAQ222-3383

 

[DODO1]

I know about the algebra (thanks anyway). But the problem is the interaction between x and i.

I came up with the following solution:
I create 2 textboxes which I name Groei and FV
The cell with the PV is a named range called PV

Private Sub groei_change()
FV.Value = Range("PV&quot.Value * ((groei.Value / 100) + 1)
End Sub

Private Sub FV_change()
groei.Value = (FV.Value / Range("PV&quot.Value - 1) * 100
End Sub

With the 2 change pieces it works, but is this the best way of doing it?

 

[johnwm]

DODO1
If you're doing it in Excel it looks OK.

BTW there is a VBA forum and an Office forum that may deal more with Excel issues, as this forum is specifically aimed at VB5/6

ADoozer
It seems to have been a maths morning

"When the only tool you have is a hammer, every problem starts to look like a nail"


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[MichaelRed]

I would pass the two potential values to a single procedure (as VARIANTS) and check the IsMissing Property (or for 'illegal value' in the parameters. To work properly, the process of setting ONE of the values should (of course) automatically clear the other. Baring the auto clear, you would need additional logic to deterine the precedence for hte calculation,



MichaelRed
m.red@att.net

Searching for employment in all the wrong places