Results 1 to 2 of 2

Math Help - Help constructing what I think is a logarithmic equation from data in MS Excel

  1. #1
    Newbie
    Joined
    Mar 2012
    From
    Ireland
    Posts
    1

    Help constructing what I think is a logarithmic equation from data in MS Excel

    Hi All
    Hopefully someone here can help me with this, it's been a long time since I studied all this and I've forgotten how to do it.

    Overview:
    I work in advertising analysis and we have a database of information which includes data on the size of each campaign (measured in TVRs) and it's corresponding Coverage (ie what percentage of the population saw the advertisment). So for example a campaign of 100 TVRs might be viewed by 55% of the population and a campaign of 200 TVRs might be viewed by 70%. I have this data for about 1000 campaigns, so there is no shortage of data.

    Problem:
    What I need is an equation that I can use to predict coverage level when I feed in a number of TVRs, and vice-versa.
    I created a Logarithmic trendline on a graph in Excel and that seemed to fit, but I noticed that the coverage is not limited to 100% as I need it to be. The Excel graph will also produce an equation for me but I would like to be able to produce the equation myself without having to make the graph each time.
    I have attached the data.
    Can you explain how I would construct the necessary equation and calculate it's component parts from the data.
    Thank you very much for your help

    Nick
    Attached Files Attached Files
    Follow Math Help Forum on Facebook and Google+

  2. #2
    Member
    Joined
    May 2008
    From
    Melbourne Australia
    Posts
    214
    Thanks
    28

    Re: Help constructing what I think is a logarithmic equation from data in MS Excel

    Let's say you have a "form" of the estimating equation that you want to use. In your case y = a Ln(x) +c and the problem is to determine values for a and c. In your case Excel has told you that a = 29.745 and c = -101.84.

    You could calculate these values yourself using matrices.

    We have the matrix equation:

    Y = X*A where:

    Y is a column matrix containing all of your coverage values
    A is a column matrix with just two elements being a and c
    X contains two columns one full of 1's and the other containing Ln of your TFNs

    i.e.
    \begin{pmatrix}y_1 \\ \vdots \\ y_n \end{pmatrix} = \begin{pmatrix}Ln(x_1)&1 \\ \vdots  &\vdots \\ Ln(X_n)  &1 \end{pmatrix} \begin{pmatrix}a \\ c \end{pmatrix}

    Now we need to solve this for the matrix A so that we get a and c.

    Y = XA
    so
    X^TY=X^TXA
    so
    (X^TX)^{-1}X^TY=(X^TX)^{-1}X^TXA=A

    (X^TX)^{-1}X^T is what we call a pseudo inverse of X.

    I am going to guess that curve fitting in Excel may be the easier option for you?
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. Replies: 2
    Last Post: May 24th 2011, 02:09 PM
  2. Constructing a Quadratic equation.
    Posted in the Algebra Forum
    Replies: 4
    Last Post: February 18th 2011, 01:10 PM
  3. Help me graph an equation in excel?
    Posted in the Algebra Forum
    Replies: 1
    Last Post: March 5th 2010, 04:07 AM
  4. Replies: 2
    Last Post: December 3rd 2009, 02:24 PM
  5. Looking for a Keyword ranking formula on a Data set in excel
    Posted in the Advanced Statistics Forum
    Replies: 0
    Last Post: April 7th 2009, 09:27 AM

Search Tags


/mathhelpforum @mathhelpforum