Increasing at Decreasing Rate using POWER and/or SQRT and/or ^

I'm not sure if this is better suited for a math 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 of the decreasing rate, 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.

Originally Posted by capheresy

I will know the value of x, which can be anything between 1.00 and 71.32 (inclusive)
[....]
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 of the decreasing rate, z1 minus 0.60 should be significantly greater than 1.00 minus z2

This is not an area of expertise for me. But the following might offer some direction.

There are an infinite number of equations y = f(x) that meet your criteria, which are subject to interpretation. The aesthetics of some might be more appealing than others.

The following table shows two such equations. The Excel formulas are explained below.

I prefer the equation on the left because it does not increase so rapidly in the beginning.

Nevertheless, for both equations, not only does the (true) rate of increase diminish as x increases, but also the difference in y diminishes for the same difference in x as x increases, which is the decreasing "rate" you describe.

	A	B	C	D	E	F	G	H	I
1	y = $A$3*LOG(x)+$B$3					y = $G$3*$F$3^(1/x)
2	m	b				m	b
3	0.215842	0.600000				0.595657	1.007291
4	x	y	rate of y chng	delta_y		x	y	rate of y chng	delta_y
5	1.000	0.6000				1.000	0.6000
6	4.516	0.7413	23.55%	0.141323		4.516	0.8981	49.6856%	0.298113
7	8.032	0.7953	7.28%	0.053975		8.032	0.9444	5.1502%	0.046255
8	11.548	0.8293	4.28%	0.034035		11.548	0.9631	1.9833%	0.018730
9	15.064	0.8542	3.00%	0.024915		15.064	0.9732	1.0526%	0.010138
:::	::::::::::	::::::::::	::::::::	::::::::::	:	::::::::::	::::::::::	:::::::::::	::::::::::
22	60.772	0.9850	0.57%	0.005587		60.772	0.9987	0.0524%	0.000523
23	64.288	0.9903	0.54%	0.005272		64.288	0.9992	0.0466%	0.000466
24	67.804	0.9953	0.50%	0.004991		67.804	0.9996	0.0418%	0.000418
25	71.320	1.0000	0.48%	0.004739		71.320	1.0000	0.0377%	0.000377

For the equation on the left (columns A:D), select A3:B3 and array-enter the following formula (press ctrl+shift+Enter instead of just Enter):

=LINEST({0.6,1},LOG({1,71.32}))

Be mindful of the difference between curly braces and regular parentheses. It would be best to copy the text above and paste it into Excel.

It returns the parameters m and b for the equation y = m*log(x)+b.

To demonstrate the formula, enter the following:

A5: 1
A6: =A5+70.32/20
Copy A6 down through A25
B5: =$A$3*LOG(A5)+$B$3
Copy B5 down through B25
C6: =B6/B5-1
D6: =B6-B5
Copy C6:D6 down through C25:D25

For the equation on the right (columns F:I), select F3:G3 and array-enter the following formula (press ctrl+shift+Enter instead of just Enter):

=LOGEST({0.6,1},1/{1,71.32})

It returns the parameters m and b for the equation y = b*m^(1/x).

To demonstrate the formula, enter the following:

F5: 1
F6: =F5+70.32/20
Copy F6 down through F25
G5: =$G$3*$F$3^(1/F5)
Copy G5 down through G25
H6: =G6/G5-1
I6: =G6-G5
Copy H6:I6 down through H25:I25

Originally Posted by capheresy

Thanks, this looks promising.

To be honest, I'm not happy with the examples I chose. I think there are better curves that offer more flexibility over the rate of change. But I'm at a loss to think of a simple one. As I said, this is not an area of expertise for me.

You might try submitting a question to Dr. Math at mathforum.com/dr.math. They are usually very responsive (within 24 hours).

Originally Posted by capheresy

Where you have 70.32, should that be 71.32?

If you follow the directions, you would see that using 70.32 causes A25 and F25 to be 71.32, the upper limit you specified. So yes, 70.32 is the right number to use.

70.32 is derived from 71.32 - 1, or more generally: upperX - lowerX. You specified that upperX is 71.32 and lowerX is 1.

Originally Posted by capheresy

If I want to play around with different what-if curves (how rapid or slow the increases are throughout the spectrum 0.60 through 1.00), what's the best way to do that? It looks to a layman like the divisor 20 has something to do with it.

Right. The divisor is the number of data points minus 1 (n - 1). I arbitrarily chose to graph 21 data points. So the divisor is 21 - 1 = 20.

Originally Posted by capheresy

It's also conceivable that I will change the 0.60 to 0.55. Would that just be a simple swap of numbers in the LINEST and LOGEST functions?

You would replace 0.6 with 0.55 wherever 0.6 appears in the formulas.

I chose to use constants to keep things simple. If you want the flexibility of playing with the limits, I suggest that you enter them into cells and change the formulas accordingly.

For example, you specified that when x is 1 and 71.32, y should be 0.6 and 1 respectively.

So put 1 into X1, 71.32 into X2, 0.6 into Y1 and 1 into Y2 (of course, you could use any pair of cells), and array-enter the following formulas (press ctrl+shift+Enter instead of just Enter):

=LINEST(Y1:Y2,LOG(X1:X2))

and/or

=LOGEST(Y1:Y2,1/X1:X2)

Somebody at a math help forum steered me to a logarithm-based solution, and if I'm not mistaken, the end result was exactly the same as your solution on the left. I just don't like the immediate jump to .665 at x=2. I'm going to play around with it, and I'll be back here if I have any more questions. Thanks so much for your help.