midget1500

folks

this has been pissing me off all afternoon.

in excel i have a graph (and it looked exponential so i added a trendline) and it give me the formula of the curve as

y = 1444e^(-0.1802x)

well the ^ and () weren't there but i used that so you know what it looks like.

so, if i plug in x as 80 i should get 96 but i get all sorts of values nowhere near this, like -7000 and such like.

can anyone get it to give the correct value?

cheers
steven

[Edited by midget1500 - 8/24/2003 6:54:51 PM]

ajm

well I get y=0.00079 !?

maybe the approximation isn't as good as you thought!

[Edited by ajm - 8/24/2003 10:38:53 PM]

midget1500

okay - as you can see the generated curve is very close so i'm guessing excel has given me the wrong formula??? don't want to rant off about good old MS, but we shall see




[Edited by midget1500 - 8/24/2003 10:52:09 PM]

ajm

try fitting a different type of curve?

midget1500

yeah, that is the closest by far - so i'm now guessing excel is showing the wrong formula - i.e., it's not like the results are a wee bit out (from the graph) but bloody lightyears away!

midget1500

ok - a log curve isn't as close but i've added that and the formula is also miles out, i.e. for x=80 it gives y=700 or something (not 96). i guess i'm either really stupid or excel is pants.



steven

ajm

Thats really weird. You don't have a random spurious data point that we can't see do you?

What are your data values, I can try duplicating the problem here...

midget1500

nope, my data is (X then Y)

10
15
20
25
30
35
40
45
50
55
60
65
70
75
80
85
90
95
100

-------------------

1,405
1,111
885
711
575
468
384
317
263
219
184
155
132
112
96
82
71
62
54

cheers
steven

ajm

Ok, I get 1729.1e^-(0.036x)

plugging in x=80

y = 97.06

What version of excel are you using? This was using Excel 2000

midget1500

man that is perfect!!!

i'm using excel 2000 (9.0.2720)

i can't believe f**king excel has just wasted about 8 hours of my sunday. bloody microsoft

cheers man, you're a star! time to find out what is wrong with excel now...(just tried a new spreadsheet with those values in case something mad was happening but nope, exact same crap)

steven

ajm

glad to be of help!

Do you have any options set in the trendline options, like trying to force an intercept?

midget1500

no - i don't have an intercept option set.

are you using that exact same revision of excel 2000?

cheers
steven

oh, and if you do set the intercept the line is visually wrong anyhow...mine looks right - just the bloody formula is wrong

[Edited by midget1500 - 8/24/2003 11:38:48 PM]

ajm

yeah exactly the same! That is really strange!

midget1500

damn it! same version - well, i'm running windows2k pro sp4 - not that this *should* make any difference. of to see if microsoft have a solution...

ajm

mines on XP pro.

I'll try it on Win2K in the morning to see if it makes any difference

midget1500

got it to work - using scatter chart instead of line chart (so, is this a bug or my very small brain being stoopid)?

many thanks
steven

ajm

ah, that sort of makes sense...

Unless you use scatter graph excel will not recognise the true values of X, it just uses the numbers you give it as labels and spaces them out evenly.

God only knows how it came up with the answer it did though!

midget1500

so line graphs in excel aren't really curves or anything, just pretty pictures whereas scatter graphs are actual mathematical models? if so then why the hell does it let you select to perform add trendline? just for visual fun? ok, then disable the formula bit - other graph types obviously don't let you add the line.

cheers anyhow - at least i can continue on my merry way

ajm

fwd to bill.gates@microsoft.com :P