+ Reply to Thread
Results 1 to 5 of 5

Exponential vs. Linear Formula

  1. #1
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2010
    Posts
    134

    Exponential vs. Linear Formula

    Experts:

    I need some assistance with potentially *tweaking* an exponential function/formula.

    Attached spreadsheet includes background and specific requirements that I'm trying to solve. As a "picture is worth a 1000 words" the explanations should make more sense once you see the data (vs. me listing all of the background in this message).

    Also, I included a 2nd tab for a "Linear" calculation... focus should be on the exponential scenario though.

    I'd welcome any feedback/recommendations for modifying the formulae.

    Thank you in advance!
    EEH
    Attached Files Attached Files

  2. #2
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,811

    Re: Exponential vs. Linear Formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In Linear sheet. Rest also same only range will change.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  3. #3
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2010
    Posts
    134

    Re: Exponential vs. Linear Formula

    avk - I am well familiar with that formula.

    My question was focused on the "Exponential" scenario. As far as the linear model is concerned, the MEDIAN does NOT address relative scoring.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,337

    Re: Exponential vs. Linear Formula

    I see a math problem. Solve the math problem, then it will be easier to solve the programming problem. Mathophobes beware!

    Now, instead, I would prefer an inverse scenario. That is, I'd like to have smaller delta values at the top (intervals 1, 2, 3...) and have larger delta valus as I near the 10th, 20th, 30th, or 100th requirement.
    If the solution gives me the larger delta at the bottom, I still must end up with zero (0) in "Y" for the last program requirments.
    Looking at the sheet, Delta is essentially y(i+1)-y(i). Because the x(i)'s are evenly spaced integers (1,2,3,...), Delta is essentially an expression of "slope". Which, to me, distills the problem down to a differential equation, initial value problem. https://calculushowto.com/initial-value-problem/

    At this point, you've only given a vague definition of dy/dx. You've said that dy/dx should be "small" when x is small (near 0) and that dy/dx should get "larger" as x increases towards x(max). At x(max), y should be 0. At this point, I would figure out more precisely what I want dy/dx to look like, then solve the initial value problem.

    Example using polynomials (because, why not?).
    Assume dy/dx is a straight line that is 0 at x=0 and decreases by 0.12 (so I can use the 0.06 that is currently in R1) for each x dy/dx=-0.12*x
    This gives me a quadratic/parabola for y y=-0.06*x^2+C
    y must be 0 at xmax -- 0=-0.06*xmax^2+C --> C=0.06*xmax^2
    final expression for y =-0.06*x^2+0.06*xmax^2=0.06*(xmax^2-x^2)

    At this point, the problem boils down to defining exactly what you want Delta to look like (with an adjustable parameter if you want). Then solve the initial value problem for that delta curve.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2010
    Posts
    134

    Re: Exponential vs. Linear Formula

    Mr. Shorty:

    I think you provided the perfect solution... I'll do some more reading on this topic but I'm very happy w/ the outcome.

    Please attached attached snapshot which I labeled "Scenario #2" (for now -- with 0.01 growth rate).

    The "Delta" are growing in DESC order... that's what I wanted.

    Again, thank you for the help!
    EEH
    Attached Images Attached Images
    Last edited by skydivetom; 05-29-2020 at 07:01 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Filling blank values in a non-linear series with linear values with a formula
    By hendo1416 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-04-2020, 12:00 AM
  2. Fill in blank cells with linear values with linear percentage increases
    By jstanley41 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-21-2018, 03:31 PM
  3. Solver: How to maximize a mark average (non-linear problem) in a linear way?
    By Sunday4th in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-07-2017, 07:42 AM
  4. [SOLVED] Exponential formula problem
    By Stan111 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-19-2015, 12:45 PM
  5. [SOLVED] Need a tricky exponential formula.
    By Polymorpher in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-22-2014, 12:25 PM
  6. linear and exponential graphs and x,y intercepts
    By Mozam.ali@asterand.com in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 11-14-2011, 01:10 AM
  7. Project exponential growth againt linear timeline
    By rgoubet@yahoo.fr in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-24-2006, 09:15 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1