Math question, not necessarily Excel...

[jennfurr]

I have an equation given to me by Excel. But being non-mathematical, I'm not quite sure how to run this through a calculator (or excel for that matter). When I try, I don't get the right type answer.

The equation:
y = -748.82Ln(x) + 4657.4

The X values are years from 1995-2001. If I try plugging 2002 into the equation, I *should* get a number around 3200 (hopefully). So how do I do it? I know nothing about natural logs.

 

[Yogi Anand]

Hi Jennfurr:

Excel has several LoG functions ... the formulas are simple enough, but I don't know what based LOG your formula requires. See the example in the simulation.

Book1
	A	B	C	D	E
2	2000	-748.82	4657.4	-1034.30778	2185.522719
3				UsingNaturalLog	Usingbase-10Log
Sheet9

_________________
Regards!
Yogi Anand

Edit:
1. added simulation
2. edited the simulation to use OP data
This message was edited by Yogi Anand on 2002-11-12 17:01

 

[Aladin Akyurek]

On 2002-11-12 16:13, jennfurr wrote:
I have an equation given to me by Excel. But being non-mathematical, I'm not quite sure how to run this through a calculator (or excel for that matter). When I try, I don't get the right type answer.

The equation:
y = -748.82Ln(x) + 4657.4

The X values are years from 1995-2001. If I try plugging 2002 into the equation, I *should* get a number around 3200 (hopefully). So how do I do it? I know nothing about natural logs.

What do you mean by "given to me by Excel"?

 

[jennfurr]

Here's the raw data. I just made a simple chart off of it, added a logarithmic trend line, then told it to display the equation. Does that help? If this were a straight-line trend I had added, I have no problems calculating a somewhat correct future prediction, but the logarithmic equation has a better fit.

1995 - 4734
1996 - 4123
1997 - 3727
1998 - 3610
1999 - 3392
2000 - 3231
2001 - 3401
2002 - ????

Just eyeballing the trend line excel put in, the answer looks like it should be around 3100.
This message was edited by jennfurr on 2002-11-12 16:47

 

[Damon Ostrander]

Hi jennfurr,

There is either an error in the equation you gave, or you have been misled into expecting a value that the function cannot produce.

For the year 1999 the equation gives -1033.93. If you give just a two-digit year, then 99 would give 1216.48, which is closer to the answer you are expecting, but I doubt that this is what is intended since you would then have to represent the year 2000 as 100 and 2001 as 101, etc.

If you can tell us something about the origin of this formula, and what it is supposed to do, perhaps we can be of some help in determining what the problem is.

But at least at this point it may be comforting to know that you are not doing anything wrong when you get values different from what you are expecting.

Damon

 

[Damon Ostrander]

Hi again jennfurr,

I think I just discovered what the problem is. I believe the equation you have is based on dates in Excel Date internal format, not just a numerical value in years. If, for example, you enter a date value for X of 1/1/2001 the formula will yield -3217, which is in the range you want except for the sign. If you want to turn the sign around just use =-(-748.82*Ln(x)+4657.4) as the formula.

With Excel dates, the actual underlying value in the cell is an elapsed number of days since 1900, NOT a value in years. If you got the equation via a curve fit of some values vs. Excel dates, this is what you would get.

 

[Aladin Akyurek]

On 2002-11-12 16:59, Damon Ostrander wrote:
Hi again jennfurr,

I think I just discovered what the problem is. I believe the equation you have is based on dates in Excel Date internal format, not just a numerical value in years. If, for example, you enter a date value for X of 1/1/2001 the formula will yield -3217, which is in the range you want except for the sign. If you want to turn the sign around just use =-(-748.82*Ln(x)+4657.4) as the formula.

With Excel dates, the actual underlying value in the cell is an elapsed number of days since 1900, NOT a value in years. If you got the equation via a curve fit of some values vs. Excel dates, this is what you would get.

What if years are numbers and not dates?

 

[Damon Ostrander]

Hi Aladin,

It shouldn't matter whether the X value is formatted as a number or a date. The Ln() function will return the natural log of the underlying value, which I now believe should be the number of days since 1900. So entering the numerical number of days, or entering the corresponding date in Date format (which causes the cell to automatically format as a date) should produce the same result. But you won't get the correct result if you enter the year as the number 2002 since this is just an integer number of years, not an Excel Date.

Keep up the good work Aladin. You're the greatest (and I notice the most prolific MrExcel poster)!

Damon