I don't think this is an easy one

[a]

Hello,

I'm not sure why this will not work - but I'm pretty sure one of you
will. The error I get when I run this is a "type mismatch". I did
declare the variables but then I undeclared them just to see whether
that was the problem.

Can anybody help?

Thanks much in advance,
Anita

node = TopValue
nodenumber = 0
degree = t - nodenumber
s = 100
x = 100
t = 16
r = 0
sd = 0.05
n = 1000

prob = "=Fact(" & t & ")" & "/" & "Fact" & (nodenumber) & "*" &
"Fact(" & degree & ") * " & 0.5 ^ nodenumber * (0.5 ^ " & degree & ")


Range("h14").Formula = prob




End Sub

 

[Tom Ogilvy]

(0.5 ^ " & degree & ")

causes the problem. You are trying to raise 0.5 to the "& degree &" power.
Looks like your quotes are messed up.

 

[Bob Phillips]

You also use t in a statement, setting degree, before you assign a value to
t, which returns a lot of zeroes in your formula.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[a]

Okay, with the help of Tom Ogilvy and Bob Phillips, this thing now
works. Now I'm wondering whether there is a way that I could have it as
a custom function. Would anybody be able to help?

Thanks in advance,
Anita



node = TopValue

s = 100
x = 100
t = 16
r = 0
sd = 0.05
n = 1000
nodenumber = 0
degree = t - nodenumber


prob = "=Fact(" & t & ")" & "/" & "(Fact" & "(" & nodenumber & ")"
& "*" & "Fact(" & degree & "))" & "*" & "(" & 0.5 ^ nodenumber & ")" &
"*" & "(" & 0.5 ^ degree & ")"
Range("h13").Formula = prob



End Sub

 

[Bob Phillips]

Quite straightforward

Function Prob
t = 16
nodenumber = 0
degree = t - nodenumber

prob = "=Fact(" & t & ")" & "/" & "(Fact" & "(" & nodenumber & _
")" & "*" & "Fact(" & degree & "))" & "*" & _
"(" & 0.5 ^ nodenumber & ")" & "*" & "(" & 0.5 ^ degree &
")"
End Function

If you want, you can pass t and nodenumber as arguments

Function Prob(t, nodenumber)
degree = t - nodenumber
prob = "=Fact(" & t & ")" & "/" & "(Fact" & "(" & nodenumber & _
")" & "*" & "Fact(" & degree & "))" & "*" & _
"(" & 0.5 ^ nodenumber & ")" & "*" & "(" & 0.5 ^ degree &
")"
End Function

and call like

=Prob(16,0)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dana DeLouis]

...whether there is a way that I could have it as

a custom function.

Do you mean as a function that returns a value? Just one idea...

Function Prob(t, nodenumber)
'= = = = =
' t!/(2^t*nodenumber!*(t - nodenumber)!)
'= = = = =
With WorksheetFunction
Prob = .Fact(t) / ((2 ^ t) * .Fact(nodenumber) * .Fact(t - nodenumber))
End With
End Function

HTH.
Dana DeLouis

 

[Dana DeLouis]

Oops! Didn't see this earlier.

Function Prob(t, nodenumber)
With WorksheetFunction
Prob = 2 ^ -t * .Combin(t, nodenumber)
End With
End Function

HTH.
Dana DeLouis