In Excel 2000, I need to create a user-defined function to execute an
exponential formula with 4 parameters: Xa, Ya, Yb, & h.

The formula is:

y = Yb + (Ya-Yb) * exp( (ln(2)/h) * (X-Xa) )

I would like the function created so that I can use it justr like the
built-in functions such as power: =power(B5,E9).

I would like to invoke my function like this:

=MyExp(B5,C5,D5,F5)

Thanks for any help or pointers.

--

On Fri, 08 Jun 2007 21:14:57 -0700, LurfysMa
wrote:

In Excel 2000, I need to create a user-defined function to execute an
exponential formula with 4 parameters: Xa, Ya, Yb, & h.

The formula is:

y = Yb + (Ya-Yb) * exp( (ln(2)/h) * (X-Xa) )

I would like the function created so that I can use it justr like the
built-in functions such as power: =power(B5,E9).

I would like to invoke my function like this:

=MyExp(B5,C5,D5,F5)

Thanks for any help or pointers.

Correction, I left off the independent variable, "x".

The function has 5 parameters: x, Xa, Ya, Yb, & h:

=MyExp(B7,B5,C5,D5,F5)


--

One way:


Public Function MyExp( _
ByVal x As Double, _
ByVal Xa As Double, _
ByVal Ya As Double, _
ByVal Yb As Double, _
ByVal h As Double) As Double
MyExp = Yb + (Ya - Yb) * Exp(Log(2) / h * (x - Xa))
End Function




In article ,
LurfysMa wrote:

On Fri, 08 Jun 2007 21:14:57 -0700, LurfysMa
wrote:

In Excel 2000, I need to create a user-defined function to execute an
exponential formula with 4 parameters: Xa, Ya, Yb, & h.

The formula is:

y = Yb + (Ya-Yb) * exp( (ln(2)/h) * (X-Xa) )

I would like the function created so that I can use it justr like the
built-in functions such as power: =power(B5,E9).

I would like to invoke my function like this:

=MyExp(B5,C5,D5,F5)

Thanks for any help or pointers.

Correction, I left off the independent variable, "x".

The function has 5 parameters: x, Xa, Ya, Yb, & h:

=MyExp(B7,B5,C5,D5,F5)


--

On Fri, 08 Jun 2007 23:44:11 -0600, JE McGimpsey
wrote:

One way:

Public Function MyExp( _
ByVal x As Double, _
ByVal Xa As Double, _
ByVal Ya As Double, _
ByVal Yb As Double, _
ByVal h As Double) As Double
MyExp = Yb + (Ya - Yb) * Exp(Log(2) / h * (x - Xa))
End Function

Perfect. Thank you.

To create this function, press Alt+F11 to open the VBA editor, then
click on Insert | Module, then paste the function code below into the
editor.

The function I provided had an error. It was missing a minus sign
before the exponent. The correct function, just tested, is:

Public Function MyExp( _
ByVal x As Double, _
ByVal Xa As Double, _
ByVal Ya As Double, _
ByVal Yb As Double, _
ByVal h As Double) As Double
MyExp = Yb + (Ya - Yb) * Exp((-Log(2) / h) * (x - Xa))
'Note: In VBA, Log() is the natural log, Log10 is the base 10 log.
' In Excel, Ln() is the natural log and Log() is the base 10 log.
End Function

In case anyone is interested, this function generates an exponential
curve that goes from (Xa,Xb) to (oo,Yb). That is, it will map a
variable (x) on the domain (Xa,oo) onto a variable (y) on the range
(Ya,Yb). This is useful for converting a half-infinite domain onto a
finite range.

This is the standard exponential decay or "half life" function. The
"h" parameter specifies how long it will take for the material to
decay to one-half of the original amount. If Ya=10 and h=3, then half
of the material will remain after in 3 time units (f(3)=5). Half of
that will remain after another 3 time units (f(6)=2.5). And so on.

If you replace the "2" with "3", then it becomes a "third life"
function and it will show when the material will decay to one third
(down by 2/3). The larger the x in ln(x), the faster the decay. The
larger the "h", the slower the decay.

What is this good for other than decaying elements?

Suppose you wanted to handicap an event based on the experience of the
contestants as measured by some rating such as the number of previous
events each contestant had participated in. You would give the largest
handicap to those with the least experience and then less for more.
Suppose the experience (number of previous events) could range from 0
to several hundred.

Suppose you wanted to award a maximum of 100 points to contestants who
had never played before (experience=0) and then fewer and fewer to
contestants who had more experience until anyone with more than 50
previous events (experience50) would get 0 points added.

The function MyExp(x,0,100,0,h) will convert the experience, x (0 <= x
,= oo), into the handicap, y (100 = y = 0).

We just need to choose h so that it goes to 0 at 100. Since
exponential functions never go to zero, we can make it to go .5 and
then round the results.

If we divide 100 by 2, it takes 7 divisions for it to get below 1:
100(0), 50(1), 25(2), 12.5(3), 6.25(4), 3.12(5), 1.56(6), .78(7),
..39(8). The exact value is 100/(log(100/.5)/log(2)) = 13.0834.

The following table was generated using the MyExp function above that
McGimpsey helped write:

h= 14.28 12.5 13.0834
x y(14.28) y(12.5) y(13.0834)
0 100.00 100.00 100.00
10 61.55 57.43 58.87
20 37.88 32.99 34.66
30 23.31 18.95 20.41
40 14.35 10.88 12.01
50 8.83 6.25 7.07
60 5.43 3.59 4.16
70 3.34 2.06 2.45
80 2.06 1.18 1.44
90 1.27 0.68 0.85
100 0.78 0.39 0.50
110 0.48 0.22 0.29
120 0.30 0.13 0.17


If anyone is interested, I also have a polynomial mappng function that
works the same way, but maps a finite domain onto a finite domain. It
can be used to shift, compress, expand, and/or skew a set of values.

PS: Thanks, McGimpsey.

--

Would you please comment on the use of ByVal
I understand what it means but is it required/recommended?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JE McGimpsey" wrote in message
...
One way:


Public Function MyExp( _
ByVal x As Double, _
ByVal Xa As Double, _
ByVal Ya As Double, _
ByVal Yb As Double, _
ByVal h As Double) As Double
MyExp = Yb + (Ya - Yb) * Exp(Log(2) / h * (x - Xa))
End Function




In article ,
LurfysMa wrote:

On Fri, 08 Jun 2007 21:14:57 -0700, LurfysMa
wrote:

In Excel 2000, I need to create a user-defined function to execute an
exponential formula with 4 parameters: Xa, Ya, Yb, & h.

The formula is:

y = Yb + (Ya-Yb) * exp( (ln(2)/h) * (X-Xa) )

I would like the function created so that I can use it justr like the
built-in functions such as power: =power(B5,E9).

I would like to invoke my function like this:

=MyExp(B5,C5,D5,F5)

Thanks for any help or pointers.

Correction, I left off the independent variable, "x".

The function has 5 parameters: x, Xa, Ya, Yb, & h:

=MyExp(B7,B5,C5,D5,F5)


--

On Sat, 9 Jun 2007 22:09:34 -0300, "Bernard Liengme"
wrote:

Would you please comment on the use of ByVal
I understand what it means but is it required/recommended?
best wishes

ByVal = "by value", which means that the "value" of the variable is
passed to the function as opposed to ByRef, which = "by reference",
which means that a reference (pointer) to the source is passed.

ByRef means that the function is working with the caller's variables,
which is usually considered bad programming. If VB6, ByRef is the
default, which is a mistake.

In VBA (for Excel), if the caller is the spreadsheet, I'm not sure it
makes much difference as I don't think ByRef will cause the source
cell to change.

I always code ByVal because I can never rememeber the defaults.


--

Guess it's at least partly a personal preference.

I tend to like to make things more explicit, rather than use defaults.

In this case, using the default ByRef won't make much of a difference,
since the UDF isn't actually trying to make assignments to any of the
arguments. It is a bit more efficient to pass ByRef (see the "Passing
Arguments Efficiently" topic in Help), but I don't think in this case
the tens of nanoseconds and 20 extra bytes on the stack will be missed.

When called from the worksheet, declaring a ByRef argument still won't
allow you to change the underlying object of the reference, so it makes
no practical difference. The exception would be if the function *did*
try to change the object, e.g.:

Public Function foo(ByRef bar as Range) As Double
bar.Value = bar.Value + 1
End Function

which will cause the value returned to the calling =foo(A1) cell to be
#VALUE!

In this case, to me, passing ByVal makes it explicit, six months down
the road when I want to update the function, that I shouldn't try to
reassign the passed arguments, nor should I expect the passed arguments
to be affected even when called from a VBA subroutine.

In article ,
"Bernard Liengme" wrote:

Would you please comment on the use of ByVal
I understand what it means but is it required/recommended?

On Sat, 09 Jun 2007 21:46:51 -0600, JE McGimpsey
wrote:

Guess it's at least partly a personal preference.

I tend to like to make things more explicit, rather than use defaults.

In this case, using the default ByRef won't make much of a difference,
since the UDF isn't actually trying to make assignments to any of the
arguments. It is a bit more efficient to pass ByRef (see the "Passing
Arguments Efficiently" topic in Help), but I don't think in this case
the tens of nanoseconds and 20 extra bytes on the stack will be missed.

When called from the worksheet, declaring a ByRef argument still won't
allow you to change the underlying object of the reference, so it makes
no practical difference. The exception would be if the function *did*
try to change the object, e.g.:

Public Function foo(ByRef bar as Range) As Double
bar.Value = bar.Value + 1
End Function

which will cause the value returned to the calling =foo(A1) cell to be
#VALUE!

In this case, to me, passing ByVal makes it explicit, six months down
the road when I want to update the function, that I shouldn't try to
reassign the passed arguments, nor should I expect the passed arguments
to be affected even when called from a VBA subroutine.

I think that last point (along with the one about being explicit) is
what really matters.


--