+ Reply to Thread
Results 1 to 3 of 3

Math function to excel formula

  1. #1
    Registered User
    Join Date
    06-06-2006
    Location
    English(UK)
    MS-Off Ver
    2016
    Posts
    82

    Math function to excel formula

    I am having some difficulty coming up with the excel version of the following function:
    f(x) = (T1 - T0)L0 + (T2-T1)L1 + (T3 - T2)L2 + ... + (x - Tn)Ln
    Constraints:
    T0=0
    Tk-1<=Tk
    Tn<= x <= Tn+1

    Tn and Ln reside in a couple of columns.
    n is variable. Both as a number of entries and as an argument to the function.

    How would I translate this to an excel cell?
    Last edited by martix; 10-20-2013 at 08:33 PM.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,476

    Re: Math function to excel formula

    I believe that one first needs to understand the math before trying to program the spreadsheet. First I need to make sure I'm understanding this function:

    1) The first constraint suggests that T is a list of values in ascending order -- is this correct?
    2) as an input variable, x is a value that fits somewhere into this list of T's, correct?
    3) x appears to determine when the summation will terminate (we will not add terms where Tn>x, correct?
    4) What does the third term look like? L2*(T3-T2)? L2*(T4-T3)? I'm having a hard time seeing the pattern based solely on the three terms given.

    With those questions aside, does this have to be a single cell function? How is x input?

    A couple of thoughts, especially if a helper column is allowed.

    a) When I program something like this (especially the first attempt), I identify the pattern for each term. Here it looks maybe like sum(L0*(Ti+1-Ti)) which would be a simple sumproduct() formula if I had columns with the L0's (which it sound like you do) and the Ti+1-Ti (which should be a simple column to add). At least until I saw a different way, I would add that helper column and then build the sumproduct formula using that helper column.
    b) Since it appears that x determines when the summation stops, I might put the conditional into this helper column =IF(Ti>x,0,Ti+1-Ti) or similar. For terms where Ti>x, the helper column will be 0 and the summation will stop.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    06-06-2006
    Location
    English(UK)
    MS-Off Ver
    2016
    Posts
    82

    Re: Math function to excel formula

    Basic idea is:
    (About as verbose as I can make it.)
    Column T defines ranges of the input(x) where the function has different rates of growth.
    Column L defines the corresponding rates of growth.

    Between T1 and T2 we grow the function by L1 for each x.
    Between T2 and T3 we grow the function by L2 for each x.
    etc.
    Between Tn and Tn+1 grow the function by Ln for each x, but stop growing at x.

    1. Not strictly. They can be equal. I.e. we can start growing directly by a higher order, say L10, skipping all the lower ones(L1-9).
    2. x is the only arbitrary range end. It is the end of the function, where we stop growing it(i.e. we're done, return result).
    3. Essentially yes.
    4. Third term would be:
    f(x) = T1L0 + (T2-T1)L1 + (T3 - T2)L2 + ... + (x - Tn)Ln
    I shortened the first term since T0==0
    [ f(x) = (T1 - T0)L0 + (T2-T1)L1 + (T3 - T2)L2 + ... + (x - Tn)Ln ]

    TEX would probably be useful, but I haven't done proper math in ages and I'm not even sure how it is in sigma notation.

    ...Continuing
    It is a parametrized function with one input and one output.
    x is one cell y = f(x) is another cell.
    or each can be a range of cells.

    My helper column is a sheet of paper really.
    However I'm thinking it's one helper per "call" of the function so not very feasible with multiple "calls" as it were.


    I did like 2 cells the hard way for validation purposes. Not something I like to repeat.
    Sooo..... I'm starting to think learning VBA and using the trusty old variables with proper control flow and loops would be easier than dealing with the idiosyncrasies of excel's formula system.

    EDIT:
    Here's some TEX.
    I think it defined properly but since I'm out of practice(as I said haven't done real math in ages), the notation might leave something to be desired.
    http://www.texpaste.com/n/mtwufba3

    And I've also started doing it in VB. Gave up on formulas.
    Last edited by martix; 10-20-2013 at 09:20 PM.

+ 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] Break Down Excel Formula All the Way to the Math
    By DHartwig35805 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-31-2013, 03:28 PM
  2. Converting a Math equation into an Excel formula
    By turnergraphics in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-04-2011, 02:06 PM
  3. Replies: 7
    Last Post: 08-02-2006, 12:50 AM
  4. Replies: 8
    Last Post: 08-02-2006, 12:50 AM
  5. Replies: 3
    Last Post: 02-16-2006, 07:00 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