# Math function to excel formula

1. ## 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?

2. ## 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.

3. ## 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.

##### Users Browsing this Thread

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

#### 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