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

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

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