Custom Function not working

I have the following

A1 = Closing Costs
A2 = 1st Mortgage
A3 = Debt Consolidation
A4 = Loan Amount (Sum of first 3 values)

A1(Closing Costs) is dependent on A4 for its calculations...So I get a circular referencing.....I tried to build a function...but when I put =ClosingCosts in Cell A1, I get a REF error in A1 and in A4...Below is my function

Function ClosingCosts(loanAmount)
If loanAmount < 100000 Then
ClosingCosts = loanAmount * 0.045
ElseIf loanAmount >= 100000 And loanAmount < 180000 Then
ClosingCosts = loanAmount * 0.035
Else
ClosingCosts = loanAmount * 0.03
End If
End Function

could someone please help with any direction...
thank you

1. You have to include the loan amount when you enter the formula
on the spreadsheet... =closingcosts(A4) or... =closingcosts(99999)

2. The function code must be entered in a general module not a sheet module.

3. You probably ought to declare the data type for the loanamount variable,
unless you specifically want it to be a Variant...

----------------
Function ClosingCosts(ByRef loanAmount As Double)
If loanAmount < 100000 Then
ClosingCosts = loanAmount * 0.045
ElseIf loanAmount >= 100000 And loanAmount < 180000 Then
ClosingCosts = loanAmount * 0.035
Else
ClosingCosts = loanAmount * 0.03
End If
End Function
----------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"jhahes"
wrote in message
I have the following

A1 = Closing Costs
A2 = 1st Mortgage
A3 = Debt Consolidation
A4 = Loan Amount (Sum of first 3 values)

A1(Closing Costs) is dependent on A4 for its calculations...So I get a
circular referencing.....I tried to build a function...but when I put
=ClosingCosts in Cell A1, I get a REF error in A1 and in A4...Below is
my function

Function ClosingCosts(loanAmount)
If loanAmount < 100000 Then
ClosingCosts = loanAmount * 0.045
ElseIf loanAmount >= 100000 And loanAmount < 180000 Then
ClosingCosts = loanAmount * 0.035
Else
ClosingCosts = loanAmount * 0.03
End If
End Function

could someone please help with any direction...
thank you
--
jhahes

Go to: Tools | Options | Calculations | Iteration and make sure the box is
checked. Read up on this parameter in the Help file to adjust the number of
iterations to one that fits your needs.

Good Luck!!
--
Thx
MSweetG222



"jhahes" wrote:

>
> I have the following
>
> A1 = Closing Costs
> A2 = 1st Mortgage
> A3 = Debt Consolidation
> A4 = Loan Amount (Sum of first 3 values)
>
> A1(Closing Costs) is dependent on A4 for its calculations...So I get a
> circular referencing.....I tried to build a function...but when I put
> =ClosingCosts in Cell A1, I get a REF error in A1 and in A4...Below is
> my function
>
> Function ClosingCosts(loanAmount)
> If loanAmount < 100000 Then
> ClosingCosts = loanAmount * 0.045
> ElseIf loanAmount >= 100000 And loanAmount < 180000 Then
> ClosingCosts = loanAmount * 0.035
> Else
> ClosingCosts = loanAmount * 0.03
> End If
> End Function
>
> could someone please help with any direction...
> thank you
>
>
> --
> jhahes
> ------------------------------------------------------------------------
> jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
> View this thread: http://www.excelforum.com/showthread...hreadid=570227
>
>