Math question, not necessarily Excel...

jennfurr

New Member
Joined
Apr 8, 2002
Messages
38
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.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
ABCDE
22000-748.824657.4-1034.307782185.522719
3UsingNaturalLogUsingbase-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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
Apr 8, 2002
Messages
38
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,334
Messages
5,595,567
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top