A = B^n - C^n (Excel VBA: Numerical Methods)

Question

I have date from multiple trials for A, B and C. I have been given the equation:

A = B^{n} - C^{n}

A,B,C => 0

B>C

From the Values of A, B and C I wish to calculate n

I have attempted to create a VBA function that calculates n using numerical methods.

Note: I am told that 0.5 <= n <= 1

Option Explicit

Private Const mdblEPSILON As Double = 0.00000001



Public Function IsEqual(ByVal dblA As Double, ByVal dblB As Double) As Boolean
    IsEqual = Abs(dblA - dblB) < mdblEPSILON
End Function

Public Function IsGreaterThan(ByVal dblA As Double, ByVal dblB As Double) As Boolean
    IsGreaterThan = (dblA > dblB) And IsEqual(dblA, dblB) = False
End Function

Function nfinder(dblConstant As Double, dblPR As Double, dblPP As Double, dblDP As Double)

Dim i As Double
Dim j As Double
Dim k As Double
Dim n(1 To 11) As Double
Dim ratiodifference(1 To 11) As Double


Dim dblA As Double
Dim dblB As Double
Dim Temporary As Double

Dim ValueLB As Double
Dim ValueUB As Double

    'This part calculates the ratiodifference for n= 0.5,0.6,...,1
    'The ratio is:
    ' 1. A / (B^{n} - C^{n}) is calculated for each n
    ' 2. This difference between this value and 1 is calculated
    ' 3. I am assuming if A = B^{truevalue.n} - C^{truevaluen} then the ratiodifference will = 0 as the ratio should = 1

For i = 1 To 6
    n(i) = 0.1 * i + 0.4
    ratiodifference(i) = Abs(1 - dblConstant / (dblPR ^ n(i) - dblPP ^ n(i)))
Next i

'I could have used redim here but i was lazy
For i = 7 To 11
    n(i) = 100
    ratiodifference(i) = 999999999999999#
Next i

' This part orders using bubble sort in order of ratiodifference
For j = 1 To 6
    For i = 1 To 5

        dblA = ratiodifference(i)
        dblB = ratiodifference(i + 1)

        If IsGreaterThan(dblA, dblB) = True Then

            Temporary = ratiodifference(i)
            ratiodifference(i) = ratiodifference(i + 1)
            ratiodifference(i + 1) = Temporary

            Temporary = n(i)
            n(i) = n(i + 1)
            n(i + 1) = Temporary

        End If

    Next i
Next j

'This part selects the smaller n of the 2 smallest ratio difference and sets this as the LowerBound
If IsGreaterThan(n(1), n(2)) Then
    Temporary = ratiodifference(1)
    ratiodifference(1) = ratiodifference(2)
    ratiodifference(2) = Temporary

    Temporary = n(1)
    n(1) = n(2)
    n(2) = Temporary
End If
ValueLB = n(1)



'Using loops the above process is repeated up to a desired amount of decimal places
For k = 2 To dblDP + 1

'Starting at the lower bound go through the decimal incriments
    For i = 1 To 10
        n(i) = ValueLB + (i - 1) * 0.1 ^ k
        ratiodifference(i) = Abs(1 - dblConstant / (dblPR ^ n(i) - dblPP ^ n(i)))
    Next i

    For j = 1 To 11
        For i = 1 To 10
            dblA = ratiodifference(i)
            dblB = ratiodifference(i + 1)

            If IsGreaterThan(dblA, dblB) = True Then
                Temporary = ratiodifference(i)
                ratiodifference(i) = ratiodifference(i + 1)
                ratiodifference(i + 1) = Temporary

                Temporary = n(i)
                n(i) = n(i + 1)
                n(i + 1) = Temporary
            End If
        Next i
    Next j

    If IsGreaterThan(n(1), n(2)) Then
        Temporary = ratiodifference(1)
        ratiodifference(1) = ratiodifference(2)
        ratiodifference(2) = Temporary

        Temporary = n(1)
        n(1) = n(2)
        n(2) = Temporary
    End If
    ValueLB = n(1)
Next k


nfinder = Round(ValueLB, dblDP)


End Function

Are there any scenarios in which this code wouldn't work or are there any changes that are needed.

Any improvements or other solutions would be appriciated! Thanks, J

@FunThomas, I think `Decimal` is a VB.Net data type, not VBA — CallumDA, Mar 07 '18 at 13:08
@Job, I presume you mean **data** rather than *date* from multiple trials? — CallumDA, Mar 07 '18 at 13:09
And when you say fail what do you mean? Can you describe and give example with outputs that don't work and ones that do? — QHarr, Mar 07 '18 at 13:14
more examples would help identify if this is a truncation/precision problem — QHarr, Mar 07 '18 at 13:27
@CallumDA: You can use `decimal` by declaring a var as `variant` and convert a number using `CDec` - but I have to admit I never used this. — FunThomas, Mar 07 '18 at 13:40
Not quite sure what this question is asking... Are you just trying to find *n* for given/known values of *A*, *B* and *C* such that `A = B^n - C^n`? — Chronocidal, Mar 07 '18 at 15:30
Checking the code, it seems that that is exactly what you are doing, so 2 questions: **1)** Why not use a Binary Search for approximation? **2)** Your example of *A* =23, *B* =100, *C* =333 can't work, since *B* < *C* and *A* >0 — Chronocidal, Mar 07 '18 at 16:35
Sorry, I am trying to approximate n given values A, B and C. And i realise now my mistake @chronocidal . How would i use a Binary search? — Job, Mar 07 '18 at 22:53
[Details are here](https://en.wikipedia.org/wiki/Bisection_method), but as a summary: Set your upper and lower bounds as 0.5 and 1.0, then try 0.75 first. If the number is larger than A, next try 0.625 - if it was less than A, try 0.875 - and keep splitting the difference to close the gap. — Chronocidal, Mar 07 '18 at 23:18
If you have the values of A,B & C, you can rearrange the equation to yield n without any macros. ````A=B^n - C^n```` becomes ````n root A = B-C````. Evaluate B-C to get the number (I'll call it D). Finally ````log of A with base D gives n```` — MTwem, Mar 02 '21 at 22:02

A = B^n - C^n (Excel VBA: Numerical Methods)

0 Answers0