Factorial using circular reference--no fact() or VBA

MrKowz

Well-known Member

Joined

Jun 30, 2008

Messages

6,653

Office Version

1. 365
2. 2016

Platform

1. Windows

• Sep 3, 2010

• #2

Why use a circular reference? Why not use the factorial function that is already in excel. =FACT(number)

 

Upvote 0

E

excelfan1

New Member

Joined

Sep 3, 2010

Messages

5

• Sep 3, 2010

• #3

If circular reference is kind of recursion then why cant we do that? i am trying to understand the use of circular reference in different scenarios.

 

Upvote 0

MrKowz

Well-known Member

Joined

Jun 30, 2008

Messages

6,653

Office Version

1. 365
2. 2016

Platform

1. Windows

• Sep 3, 2010

• #4

I don't believe that factorial can be obtained through a circular reference. The reason is that you must continually reduce the multiplier by 1. This can be easily achieved through VBA, but since it requires 2 numbers that dynamically change, not one, a circular calculation won't work.

 

Upvote 0

E

excelfan1

New Member

Joined

Sep 3, 2010

Messages

5

• Sep 3, 2010

• #5

Ok ..so what about square root ? can it be done?

 

Upvote 0

Ruddles

Well-known Member

Joined

Aug 24, 2010

Messages

5,851

Office Version

1. 365

Platform

1. Windows

• Sep 3, 2010

• #6

excelfan1 said:

just wanted to check whether we can calculate factorial of a number using circular reference.

Click to expand...

If by 'circular reference' you mean recursion, then yes, the calculation of a factorial is ideally suited to this very elegant programming technique.

Here's a factorial function I whisked up just for you:-

Code:

Option Explicit
 
Public Function xFactor(arg As Integer) As Long
 
  If arg = 1 Then
    xFactor = 1  
  Else 
    xFactor = arg * xFactor(arg - 1)
  End If
 
End Function

Enjoy!

 

Upvote 0

mikerickson

MrExcel MVP

Joined

Jan 15, 2007

Messages

24,355

• Sep 3, 2010

• #7

Can what be done?

Replacing the SQRT function could be done with EXP(LN(A1)/2).

But recursion is not a way to build a SQRT emulator.

What are you trying to do? Why do you want Circular References?

 

Upvote 0

E

excelfan1

New Member

Joined

Sep 3, 2010

Messages

5

• Sep 3, 2010

• #8

Sir,

I want to understand the use of circular reference in any recursive scenario. Now for example lets take sqrt of a number

In Cell A1 81 ( number you want to find sqrt)
In cell A2 enter =A4
In cell A3 enter =A1/A2
In cell A4 enter =average(A2:A3)

Enable iteration in excel and A4 gives you square root.

I was just trying to use the same for factorial but cant make it to work.

 

Upvote 0

E

excelfan1

New Member

Joined

Sep 3, 2010

Messages

5

• Sep 3, 2010

• #9

I know that i have not got any answer so far ..but i still believe this is the only place from where i can get one.

 

Upvote 0

Ruddles

Well-known Member

Joined

Aug 24, 2010

Messages

5,851

Office Version

1. 365

Platform

1. Windows

• Sep 3, 2010

• #10

excelfan1 said:

what about square root ? can it be done?

Click to expand...

Probably not. You would do that using a technique known as 'approximation by iteration', something like this:-

Code:

Option Explicit
 
Public Function xSqRoot(arg As Double) As Double
 
  Dim Root1 As Double
  Dim Root2 As Double
  Dim iLoop As Long
 
  Debug.Print "Square root of" ; arg ; "by iteration"
  Root1 = 1
  For iLoop = 1 To 20 ' loop as many times as necessary to home in on a number which doesn't change from one loop to the next
    Root2 = arg / Root1
    Root1 = (Root1 + Root2) / 2
    Debug.Print "Loop " ; iLoop ; ": " ; Root1 ' view the immediate window to see this happening
  Next iLoop
 
End Function

The problem with recursion is that you must always ensure there's a 'way out'. In the factorial example, the way out is toi return 1 when the function is called with an argument of 1.

It would be interesting to know what you're trying to achieve...

 

Last edited: Sep 3, 2010

Upvote 0