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 (experience>50) 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.
--
