0

I have a graph in Desmos which I use to create a formula I can use in excel. I do this as Desmos has some easy slider functionality for people who don't 100% know what they're doing, like me. I'm not trained in mathematics.

I have managed to work out that the curve I have is called 'exponential decay'. The x axis is time in minutes and the y axis is money per minute, and using y=a(b^x)+c will usually get me around where I want the line (I think I've managed to attach a screenshot from Desmos). I think that c then represents a minimum 'money per minute' value (so the further in time the graph goes, the closer the 'money per minute' value gets to c), but I'm not so sure what a and b are doing/representing.

Ideally, the curved line would remain within the 'top-right hand' corner of the graph as it were. Neither x nor y should be negative. Now, I seem to have worked this out for y, as the +c seems to limit the line from having a negative y value, but is there a way I can do this so that x doesn't become negative? Again, I'm not 100% sure I'm even using the correct 'format' with the y=a(b^x)+c thing, so I'm open to any suggestions. Sorry if my language is incorrect too, I'm not au fait with correct maths terms.

The end result is that a, b and c become static terms in excel. I use a formula so that a user can input any x value they like (so they will always input a positive integer), and excel will return a corresponding y term.

So, if anyone has followed along so far, my questions are-

  • if it's easy to explain in layman's terms, what are a and b doing?
  • is there a way to limit the curve to the top-right quadrant of the graph?
  • is it correct to use y=a(b^x)+c?

If it helps, this is the excel formula to give y:

=ROUNDUP([minutes input cell]*(54.9*(POWER(0.97,[minutes input cell]))+17.2),0)

Desmos screenshot

Many thanks internet strangers <3

Henry
  • 157,058
Exponentially_Dumb
  • 3
  • Does the curve go exactly through the three points whose coordinates are listed in the chart to the left of the graph? If so it may be possible to deduce the curve in your form. – coffeemath Feb 04 '22 at 11:54
  • If you want to prevent $y$ ever being negative then you want $c \ge 0$. Meanwhile $y=a+c$ is the value when $x=0$, so if that is the start point then $a$ is the difference between the start and the limit on the right. $b$ is related to the rate of decay: $b$ just above $0$ will have faster decay than $b$ just below $1$ – Henry Feb 04 '22 at 11:55
  • Hi Henry, I think I already have that for y, but I was wondering on x please? – Exponentially_Dumb Feb 04 '22 at 11:58
  • Hi coffeemath, Is that possible to do? I'd love to be able to get a, b and c exactly without using the sliders? – Exponentially_Dumb Feb 04 '22 at 11:59

1 Answers1

1

You have three data points $(x_i,y_i)$ (all different) and you want to adjust $(a,b,c)$ for the model $y=a \,b^x+c$. Writing the equations $$a \,b^{x_1}+c-y_1=0 \tag 1$$ $$a \,b^{x_2}+c-y_2=0 \tag 2$$ $$a \,b^{x_3}+c-y_3=0 \tag 3$$ $$(2)-(1) \implies a(b^{x_2}-b^{x_1})=y_2-y_1\implies \color{red}{a=\frac{y_2-y_1 }{b^{x_2}-b^{x_1} }}\tag 4$$ Plug $a$ in $(1)$ and solve for $c$ $$\color{red}{c=\frac{y_1\,b^{x_2}-y_2\,b^{x_1} }{b^{x_2}-b^{x_1} }}\tag 5$$ Plug now in $(3)$ and simplify. You then need to solve for $b$ the equation $$(y_2-y_3)b^{x_1}+(y_3-y_1)b^{x_2}+(y_1-y_2)b^{x_3}=0 \tag 6$$ which, in the most general case, will not show explicit solution and would require some (easy) numerical method.

However, if the $x_i$ are in arithmetic progression, that is to say $x_2=x_1+\Delta$ and $x_3=x_1+2\Delta$, equation $(6)$ reduces to $$b^{x_1}(b^\Delta-1)\Big[(y_1-y_2)b^\Delta+(y_3-y_2)\Big]=0 \implies \color{red}{b=\Bigg[\frac{y_2-y_3 }{y_1-y_2 }\Bigg]^{\frac 1 \Delta }}\tag 7$$

Using the numbers $(60,26.27),(90,20.72),(120,18.69)$, we have $$b=\left(\frac{203}{555}\right)^{\frac{1}{30 }}=0.96703\cdots\quad\implies\quad a=65.4090\cdots\quad \text{and} \quad c=17.5193\cdots$$ which are strictly exact while Excel's results are not.

Now, if you want $x$ from $y$, using logarithms $$x=\frac{\log \left(\frac{y-c}{a}\right)}{\log (b)}$$

Claude Leibovici
  • 260,315
  • I can't express how much I appreciate this @Claude Leibovici ! Many, many thanks. I think I understand most of this to be able to shuffle it into excel. I know for sure I've seen a LOG function. Do you have any tips on what the Delta is called though? I recognise it as change from physics classes many moons ago. So the first instance where you have used it x2 = x1 + D, you are saying x2 is equal to x1 and a change in something? Does D then have the same value when you use it in the index 1/D in red? – Exponentially_Dumb Feb 04 '22 at 14:18
  • $\Delta$ is whatever you want. Name it John_Doe if you wish. ! Cheers :-) – Claude Leibovici Feb 04 '22 at 14:21
  • That was quick! Thank you! – Exponentially_Dumb Feb 04 '22 at 14:21