Correlating range of number - negative exponential curve?

Question

Hello Support

I am a novice/beginner user of Excel and need some help to create, what I think is, a negative exponential curve.  I wish to be able to key in a number from 1 to 100000 in one cell and have it display the result in another.  Specifically, when keying in 1 I need it to display 3.50 and when keying in 100000 for it to display 1.00.  I also need lower number keyed in to decrease the results faster to when keying in larger numbers (hence, what I think is a negative exponential curve). Is someone out there able to tell me (if possible at all) how to write up a formula that would accomplish this goal?

Answer 1

Write some XY values into column A

Then create a XY-Scatter chart with smooth lines and markes

Right-Click the series and add a trendline, check "Display Equation on Chart"

Make a screenshot same as shown below and post it here

How to include a screenshot in your post - Microsoft Community

Andreas.

Answer 2

Hello Andreas

Thanks for you input.  I am actually looking for the ability to key in a number from 1 to 100000 in one cell (lets say A2) and display the results in cell B2 (the B2 value will be used for further calculations).  I don't need a graph.  Is this possible?  If so, can you show the actual formulate required. Much appreciated. 

Björn

Answer 3

There are an infinite number of formulas (formulates?)

that go through your two given points.

An additional hundred points would pare it down somewhat.

Answer 4

need some help to create, what I think is, a negative exponential curve.  [....] when keying in 1 I need it to display 3.50 and when keying in 100000 for it to display 1.00.

looking for the ability to key in a number from 1 to 100000 in one cell (lets say A2) and display the results in cell B2

Yes, with only 2 data points, there are an infinite number of "curves", including a straight line.

But I suspect that you want the following in B2:

=GROWTH({3.5; 1}, {1;100000}, A2)

The GROWTH function derives the same values that the chart exponential trendline derives, despite a different form of an exponential formula. (*)

(Beware of regional syntax differences.  You might write =GROWTH({3,5; 1}; {1; 100000}; A2) .)

---

(*) The linear regression (LOGEST) of the GROWTH formula y = b * m1^x is equal to the linear regression (LINEST) of the formula for the chart exponential trendline y = b * EXP(m2*x), where m2 = ln(m1), within the limitations of 64-bit binary floating-point.

This is demonstrated below.

Formulas:

B7:      =GROWTH($B$2:$B$3, $A$2:$A$3, A7)

C7:      =$B$12 * $A$12^A7

D7:      =$C$15 * EXP($A$15*A7)

A12:B12: =LOGEST($B$2:$B$3, $A$2:$A$3)       array-enter; press ctrl+shift+Enter

C12:     =LN(A12)

A15:B15: =LINEST(LN($B$2:$B$3),$A$2:$A$3)    array-enter; press ctrl+shift+Enter

C15:     =EXP(B15)

Answer 5

Thanks Joeu2004

The GROWTH function has great promise and does what I asked for.  Thanks again.  If I may impose on you with a tweak to the formula you presented above.  Is is possible to add a factor to the formula that will bend the results so that the curve drops steeper (lets say up to 20000 key-in value of A2) then a more gentle slope up to 100000.  

The goal here is to estimate time/unit where smaller number of units will have a larger per unit factor and larger number have a smaller per unit factor. There may already be an formula for this out in the business world. 

Björn