Maintaining relationship between 3 variables
 

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.

Maintaining relationship between 3 variables
 

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

NickHK


egroups.com...
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.

Maintaining relationship between 3 variables
 

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 wrote:
If, say, RNominal changes, how do know which of the other 2 stays constant
and which varies ?

NickHK


egroups.com...
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.

Maintaining relationship between 3 variables
 

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


groups.com...
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 wrote:
If, say, RNominal changes, how do know which of the other 2 stays
constant
and which varies ?

NickHK


egroups.com...
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.

Maintaining relationship between 3 variables
 

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

Mike


NickHK wrote:
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


groups.com...
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 wrote:
If, say, RNominal changes, how do know which of the other 2 stays
constant
and which varies ?

NickHK


egroups.com...
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.