Maintaining relationship between 3 variables

[emerging_markets]

I have 3 variables: a 'real' rate named Rreal in cell D4, a
'nominal' rate named Rnominal in cell D7, and an inflation rate
named Inflation in cell J7.

The relationship between these variables should be as follows:

( 1 + ( Rnominal / 1000 ) ) = ( 1 + ( Rreal / 1000 ) ) * ( 1 + (
Inflation / 1000 ) )


I would like a macro that automatically runs whenever a value changes
in D4, D7 or F7, that maintains the relationship between the variables.
That is, the nominal rate is recalculated automatically if, say, the
inflation rate is changed, or the implied inflation rate changes if
either the nominal rate or real rate is changed.

Can anyone show me what the macro would look like? Thanks in advance.

 

[NickHK]

If, say, RNominal changes, how do know which of the other 2 stays constant
and which varies ?

NickHK

 

[emerging_markets]

Thanks Nick

Good question, I didn't think of that (albeit obvious now you mention
it). Perhaps a default rule could be applied? i.e. (a) if Rnominal
changes, recalculate the Rreal for a given Inflation, (b) if Inflation
changes, recalculate Rnominal for a given Rreal, and (c) if Rreal
changes, recalculate Rnominal for a given Inflation.

Does this help?

Mike

 

[NickHK]

Mike,
Here's one way :
Name the 3 cells accordingly.

Private Sub Worksheet_Change(ByVal Target As Range)
'(1 + (Rnominal / 1000)) = (1 + (Rreal / 1000)) * (1 + (Inflation / 1000))

'Avoid keep firing this change event
Application.EnableEvents = False

Select Case True
Case Not Intersect(Target, Range("RNominal")) Is Nothing
Range("RReal").Value = Range("RNominal").Value /
Range("RInflation").Value
Case Not Intersect(Target, Range("RInflation")) Is Nothing
Range("RNominal").Value = Range("RInflation").Value /
Range("RReal").Value
Case Not Intersect(Target, Range("RReal")) Is Nothing
Range("RNominal").Value = Range("RReal").Value /
Range("RInflation").Value
End Select

'Reset event
Application.EnableEvents = True

End Sub

You should add error handling also.
I have left implementing the correct formulae as an exercise for the reader.

NickHK

 

[emerging_markets]

Nick - many thanks for your pointers, I got it to work fine in the end

Mike