PDA

View Full Version : quadratics in excel?



ravage13
30-03-2004, 02:39 AM
Hi
I've been plotting some graphs of sodium emission (chemistry related, but don't let that put you off) on excel which generate curves that perfectly fit a quadratic (ax^2+bx+c=0) and was wondering if anyone knows if there is a built function that will solve for x as it is a real pain solving them manually :-)

cheers

ravage

MrBeef
30-03-2004, 07:01 AM
good ole, x = -b Ī (square root) b≤ - 4ac / 2a

Um, i am not to sure, im in 6th form we just learnt that

rugila
30-03-2004, 09:45 AM
Put any value for x into a cell say 2 in cell A1

Write down your function a*(A1)^2+b*(A1)+c in a cell, say A2.

(you presumably know the numerical values of a, b and c in advance, but if you want to treat them as parameters i.e. changeable, it may be better to enter them in separate cells and use the cell references in your A2 formula)

Go to the menu > tools > goal seek

Enter set cell A2
To value 0
By changing cell A1

The resulting value in A1 should be what you want.

Post back if this isn't the approach you want to take.

rugila
30-03-2004, 10:08 AM
Couple of further points I suppose should be made.
Depends on how far down the track you are with that sort of stuff.
I don't think I should be posting this sort of stuff in this forum tho'.

1. Quadratic equations (with real parameters) may have two real solutions, or may have no real solutions. ("real" as used here is a mathematical term with little connection to everyday ideas about reality.)
It may be that the Excel goal seek will give you either the "wrong" solution (from the endpoint of your practical chemistry that is) or give an error.
You can analyse this further with the form 6 or whatever quadratic formula or (pace Biff) by other means.

2. In the event that a quadratic does not give perfect fit, try a cubic by the same approach. Cubic always has at least one real solution, and may have three (but not two). Likewise can analyse cubic further with a formula, if you happen to like formulae.

4. Quartic (fourth power equation can do likewise)

5. Quintic or higher power polynomial equations, have no formulae giving explicit solutions, and you have to use successive approximation methods (Horner, Newton-Raphson, Gauss-Seidel, etc. or whatever it is that Excel uses.

6. Main thing is to have the abilty to reject solutions that are not relevant to your problem (although this is a pretty tough point - even some very good people toss out solutions that they would have learnt a lot from if examined more closely)

7. If the Excel goal seek approach turns up an answer (say with your quadratic) that you consider irrelevant, try a different initial value in cell A1 or, better, use the Excel solver to examine the senstivity of the solution to changing initial values.

Graham L
30-03-2004, 05:02 PM
Whadday mean "wrong solution"? It comes out of a computer, dunnit?

:D]:)

After all economists, politicians, experts, just put numbers into spreadsheets, and extrapolate. They then believe the answers.

In fact some years ago there was a serious warning that Excel spreadsheets should not be used for scientific work which was to be published in some of the journals. There were major flaws in some of the statistical builtin functions. They may have been fixed. :D

rugila
30-03-2004, 05:44 PM
Appreciated your interesting contribution, Graham L.

You show a refreshing openness in displaying your lack of knowledge of the topics discussed for all to appraise.

Rather reminds me of the political meeting where a heckler called out ďTell us all you know, rugila, itíll only take a minuteĒ, quickly eliciting the obvious response ďIíll tell them all we both know, Graham L, it wonít take any longerĒ.

On the other hand maybe Iím misjudging and youíre only practicing for your forthcoming audition with the John Cleese show. No need to wish you well, since you already have all the makings of the Compleat Comedian (acknowledgement to Isaac Waltonís book ďThe Compleat AnglerĒ for teaching me the appropriate spelling).

ravage13
31-03-2004, 12:47 AM
Mr Beef
That formula (had to dredge it up-been years since I used it) was what I used once I finally got hold of a calculator
was trying to use excel as my calculator and had to break it up into several chunk to get it to work properly (especially when you have several graphs and many y values) which took ages
was just hoping excel had a quick method for finding x

good to see they still teach quadratics at 6th form (did they make you derive the equation yourself :-)

cheers
ravage

ravage13
31-03-2004, 01:01 AM
Hey rugila
whadda ya know
that method does work (after a fashion)
got the same result as with a calculator once I sorted everything out

cheers

ravage

Graham L
31-03-2004, 04:23 PM
Rugula: I attempt from time to time to remind people that it is not wise to trust any computer output. If the light tone upsets you, that is your problem.

Perhaps the work by BD McCullough (from 1990) on Excel's statistical reliability has escaped your extensive studies. McCullough's suggestion was " ... persons desiring to conduct statistical analysis of data are advised not to use Excel".

The problems exist. The open source gnumeric programme was, it seems, such a good clone of Excel that it had the same erroneous algorithms. The errors were reported to the developers, and gnumeric was fixed. Reportedly, Excel has not been fixed.



Berkeley Labs (http://www.lbl.gov/ICSD/CIS/compnews/2000/June/05_journal.html) discusses the problem.

Here's a few more ...
Wolfram (http://www.wolfram.com/news/statistics.html).(make Mathematica).
SPSS (http://www.spss.com/research/wilkinson/Publications/Accuracy.pdf). (make one of the standard stats packages.
A university course (http://facweb.arch.ohio-state.edu/pviton/courses2/crp8703/8703syl.html)(see the comments in the "Computer Programs" section).

I have written statistical analysis software. I know how tricky it is. I don't trust computer output. I don't trust spreadsheets.

I see the questioner is smart enough to compare the results with check calculations.

rugila
01-04-2004, 12:39 AM
Once again I appreciated your interesting contribution, Graham L.

>If the light tone upsets you, that is your problem.
No problem at all, I enjoyed the light tone, hence responded in kind. Hope I didnít upset you.

>Perhaps the work by BD McCullough (from 1990) on Excel's statistical reliability has escaped your extensive studies. McCullough's suggestion was " ... persons desiring to conduct statistical analysis of data are advised not to use Excel".

Excel's statistical reliability or lack of it is totally absorbing, but one scarcely needs to do extensive (or indeed any) studies on that or your references to solve simple quadratic equations. Thatís a very elementary numerical (not statistical) calculation which even Excel is unlikely to get wrong.

Since you seem to want to cast doubt on Excelís quadratic-solving abilities, no doubt you can easily back that up. Say by providing this forum with one, just one, counterexample where Excel fails to solve a quadratic, or does so with an ďinsufficientĒ degree of accuracy.
If you do this Iím more than happy to concede that Excel is inadequate in this respect.
Of course if you canít Iíd reluctantly have to concede that your allegations in this particular context have no substance whatever.

And just out of interest, you note that:
>I see the questioner is smart enough to compare the results with check calculations.
Itís easy enough produce an example where Mr Beefís formula fails but where the method I suggested for Excel succeeds. With your clear expertise on the topic and your desire to spread your wisdom to the world, I anticipate you are itching for the opportunity to show what you know by telling us about this.


Enough on quadratics though. Since you have an interest in statistical matters and statistical reliability, letís discuss those a little further.
One scarcely needs to go through your references to find things that are wrong with Excelís statistical packages.
Try its regression for example. The output duplicates the upper and lower 95% fiducial limits (would you call them confidence intervals? are you right?) merely because someone years ago thought that 95% in the heading was different from 95.0%.
This is just a display bug, not a misleading inaccuracy.
Rather worse, but less obvious, is that Excelís 2-factor anovaís treat empty cells as cells as if they contained zero. This certainly gives incorrect results. Worse, they do this without telling you, and itís very true that anyone THAT DIDNíT KNOW WHAT THEY WERE DOING would probably be lead astray. On the other hand, itís doubtful if anyone that didnít know what they were doing should be using these tools anyway other than as experience-gaining devices, and certainly not in such a way as to claim they were (statistically) proving scientific or other propositions.

Personally, I make a moderate use of Excelís statistical tools because they are often convenient. But only in cases where I have independently verified just what the particular tool does, and where it is likely to go wrong. Excel isnít the only deficient commercial statistical program. Did you ever try SAS, itís one of the few to attempt singular regressions, but does a pretty poor job with them? Soritech, Shazam and (I think) SPSS donít even attempt that. Itís got other problems too.

Like you, I write statistical software from time to time.

Iíd like to discuss some of the stuff in your references more but itís getting late and Iíve got an interesting day tomorrow (today?).
Eg. Your
> A university course(see the comments in the "Computer Programs" section).
includes references to a few books by the guy running the course. Doesnít look like heís kept up with his own reading since his references are pretty dated. Eg. Bill Greene had a 1993 second edition of his book, whereas the course only notes his 1990 edition. Allen Craig (in ref Hogg and Craig) was teaching statistics at Iowa State Uni way back in the mid-1930ís. Iowa was one of only 3 institutions teaching mathematical statistics in the States at that time (find that one on Google if you can!). Craig taught Frank Haight, an American who introduced teaching of mathematical statistics into New Zealand around 1947 and who was the first (and only) person to be awarded a PhD in mathematics by the University of New Zealand.

Whatís all this got to do with simple numerical solution of quadratics anyway? Iím not even a statistician Ė never have been.