CODEWITHSUNDEEP
excelvbamath
geddeca
geddeca

Reputation: 119

Excel Function - Can't figure out the equation needed

I am using a loop to figure out the ending rent after an increase of 15% per year. This works fine.

    Private Sub Worksheet_Change(ByVal Target As range)
    On Error GoTo ErrorHandler '<--| be sure to catch any error and enable events handling back
    Select Case Target.Address

        Case "$F$15"
           'Recalculate as necessary Using Looping
          amount = range("F12").Value
          For counter = 1 To range("F15").Value
             Debug.Print counter;

             amount = amount + (range("F12").Value * 0.15)
           Next
           range("F16").Value = amount

The problem for me is that I wish to have the same kind of loop to figure out the opposite scenario also. I want to be able to Figure out what the Beginning rent would have been depending on the years entered. (F21) Assuming that we know the ending Rent. Excel Screen Shot

So need to figure out F20. I asked my son and he worked it out using algebra but couldn't help me with excel. Thanks for any suggestions.

Upvotes: 0

Views: 59

Answers (1)

YowE3K
YowE3K

Reputation: 23984

I have refactored your code, using both a looping method and a non-looping method:

Private Sub Worksheet_Change(ByVal Target As range)
    On Error GoTo ErrorHandler '<--| be sure to catch any error and enable events handling back
    Dim factor As Double
    Dim period As Integer
    Dim amount As Double
    Select Case Target.Address

        Case "$F$15"
            period = Range("F15").Value
            amount = Range("F12").Value

            'Recalculate as necessary Using Looping
            factor = 1
            For counter = 1 To period
                Debug.Print counter;
                factor = factor + 0.15
            Next
            Range("F16").Value = amount * factor

            'alternate method without looping
            Range("F16").Value = amount * (1 + period * 0.15)
            'which can also be written in VBA as
            [F16] = [F12] * (1 + [F15] * 0.15)

        Case "$F$21"
            period = Range("F21").Value
            amount = Range("F19").Value

            'Recalculate as necessary Using Looping
            factor = 1
            For counter = 1 To period
                factor = factor + 0.15
            Next
            Range("F20").Value = amount / factor

            'alternate method without looping
            Range("F20").Value = amount / (1 + period * 0.15)
            'which can also be written in VBA as
            [F20] = [F19] / (1 + [F21] * 0.15)

Upvotes: 2

Related Questions