# Thread: Increasing at a Decreasing Rate

1. ## Increasing at a Decreasing Rate

I'm not sure if this is better suited for an Excel forum, but here goes.

I will know the value of x, which can be anything between 1.00 and 71.32 (inclusive), and can have any number of decimal places to the right, such as 20.123456789.

An x value of EXACTLY 1.00 must return a value of EXACTLY 0.60
An x value of EXACTLY 71.32 must return a value of EXACTLY 1.00

The result values in the continuous range 0.60 to 1.00 must increase at a Decreasing rate. For example,
(1) An x value of around 2 should return a value greater than 0.60, call it z1
(2) An x value of around 70.32 should return a value less than 1.00, call it z2
Because I want a decreasing rate of increase, z1 minus 0.60 should be significantly greater than 1.00 minus z2

It seems like raising x to a power that is in the range 0 to 1 should provide the increase at a Decreasing rate, but nailing down the correct power that enforces both endpoints is where I'm stuck.

2. ## Re: Increasing at a Decreasing Rate

Originally Posted by capheresy
I'm not sure if this is better suited for an Excel forum, but here goes.

I will know the value of x, which can be anything between 1.00 and 71.32 (inclusive), and can have any number of decimal places to the right, such as 20.123456789.

An x value of EXACTLY 1.00 must return a value of EXACTLY 0.60
An x value of EXACTLY 71.32 must return a value of EXACTLY 1.00

The result values in the continuous range 0.60 to 1.00 must increase at a Decreasing rate. For example,
(1) An x value of around 2 should return a value greater than 0.60, call it z1
(2) An x value of around 70.32 should return a value less than 1.00, call it z2
Because I want a decreasing rate of increase, z1 minus 0.60 should be significantly greater than 1.00 minus z2

It seems like raising x to a power that is in the range 0 to 1 should provide the increase at a Decreasing rate, but nailing down the correct power that enforces both endpoints is where I'm stuck.
$\large a \ln(x/b)$ should get you what you want.

solving you obtain

$\large a=\dfrac{2}{5 \log \left(\frac{1783}{25}\right)}$

$\large b=\dfrac{125}{1783 \sqrt{1783}}$

3. ## Re: Increasing at a Decreasing Rate

I haven't used logarithms in many years, so this is a struggle. Anyway, I put the following into Excel and I'm getting a #VALUE! message:

Cell A1 is a = 2/LOG(1783/25,5)
Cell A2 is b = 125/(1783*SQRT(1783))
Cell A3 is x = 20.123456789

Cell A4 is the result = A1*LN(A3/A2)

Please tell me where I'm screwing up before I move on to the Excel forum. Thanks much.

4. ## Re: Increasing at a Decreasing Rate

I fixed the value error (a dumb mistake). I tried it with x=1.00, which should return 0.60, but instead I get 4.828

5. ## Re: Increasing at a Decreasing Rate

Originally Posted by capheresy
I haven't used logarithms in many years, so this is a struggle. Anyway, I put the following into Excel and I'm getting a #VALUE! message:

Cell A1 is a = 2/LOG(1783/25,5)
Cell A2 is b = 125/(1783*SQRT(1783))
Cell A3 is x = 20.123456789

Cell A4 is the result = A1*LN(A3/A2)

Please tell me where I'm screwing up before I move on to the Excel forum. Thanks much.
I have

a1= =2/(5*LN(1783/25))

a2 =125/(1783*SQRT(1783))

a3 = whatever x

a4 = $a$1*LN(a3/$a$2)

6. ## Re: Increasing at a Decreasing Rate

Fantastic. I was using 5 as the base for Excel's LOG function. Thank you very much. Now, we'll see how well my college football rating system works...

7. ## Re: Increasing at a Decreasing Rate

One more thing, if I may. Each season the 71.32 number will be different. Could you explain where in the formula I would make the change(s)?

8. ## Re: Increasing at a Decreasing Rate

Originally Posted by capheresy
One more thing, if I may. Each season the 71.32 number will be different. Could you explain where in the formula I would make the change(s)?
Let $var$ be the number that is currently $73.32$

$a=\dfrac{2}{5 \ln(var)}$

$b=(var)^{-3/2}$

so

a1 = 2/(5*LN(var))

a2 = (var)^(-3/2)

maybe what you'd want to do is make

a1=var

and shift a1 and a2 down one cell to become a2 and a3, then replace var with a1 in the formulas.

9. ## Re: Increasing at a Decreasing Rate

Thank you again!!!

10. ## Re: Increasing at a Decreasing Rate

I thought I was done, but no. If I decided that the slope of the rate is too steep for the lower part of the range, how could I play around with different what-if slopes (rates) to find the optimum one (in my mind)? I can see that your 1783 is 25*71.32, and your 125 is 25*5, so I'm guessing that the number 5 and/or the -3/2 power is controlling the steepness. I will appreciate any further help I can get.