Curve fitting in excel 2007

[Cerium]

Hallo,

Ik heb zopas een grafiek getekend in Excel, een spreidingsgrafiek. Daaraan heb ik een trendlijn toegevoegd (een veelterm van graad 4) en daarbij heb ik de vergelijking opgevraagd. Zie hieronder de grafiek met vergelijking:

grafiek 1046 keer bekeken

Als ik deze veelterm nu evalueer in x = 125 levert mij dat de waarde 1,10 op. Maar in de grafiek is duidelijk te zien dat de curve onder 1 blijft. Wat loopt er hier mis? Kan deze vergelijking dan niet gebruikt worden voor interpolatie? Als dat inderdaad niet gaat, hoe moet ik dit probleem dan wel aanpakken?

Alvast bedankt

[E.Desart]

De reden is dat Excel standaard (om visuele redenen) het aantal "significant digits" beperkt (in die getoonde formule).

Selecteer die formule en met je aantal decimalen symbool (in je toolbar), vergroot je gewoon het aantal digits na de komma. Wanneer alleen nog maar nullen verschijnen zitten je buiten Excels nauwkeurigheidslimiet.

Excel rekent met (of toont) ca 15 "significant digits". Die cijfers na de komma zijn behoorlijk belangrijk in dergelijke regressieformules. Ruwweg: Hoe hoger de macht van de variabele hoe belangrijker.

[Cerium]

Bedankt voor je snelle reactie! Daar had ik ook al aan gedacht. Maar ik vind geen manier om de vergelijking van die polynoom in een cel te krijgen zodat ik er verder mee kan rekenen. Is dit eigenlijk wel mogelijk?

[E.Desart]

Werk jij met Engelse of Nederlandse Excel? (zou hier best standaard vermeld worden bij vragen).

In de Engelstalige: kijk naar de functies LOGEST en LINEST, die je alle parameters laten berekenen van zo'n regressie. Dit vraagt ook wat werk, want dit geeft geen simpele formule maar alle individuele parameters in een array.

Ik heb mijn macro nu niet geïnstalleerd voor vertalingen van functies (Excel geherinstalleerd).

Sedert Excel 2003 werken alle statistische functie met dezelfde nauwkeurigheid als die Grafiektrendline.

Dit was niet zo voor Excel 2003 (is dus een belangrijke verbetering voor mensen die met statistiek werken).

Je kan echter ook die formule uit de grafiek kopiëren en aanpassen.

Wanneer je dit echter dynamisch moet doen, moet je via die functies gaan en uit die losse parameters je eindformule samenstellen in een andere cel. Ik heb hier nu geen pasklaar voorbeeld.

Er bestaat wel een macro (maar ook die zou ik moeten gaan zoeken) die de formule uit die grafiek kan extraheren, maar ook deze werkt niet dynamisch als een formule. Deze macro werd gemaakt omdat tot Excel 2002 er een duidelijk verschil in nauwkeurigheid was tussen de trendlijnformule in de grafiek en de functieresultaten via het werkblad.

Met Excel 2003 is de statistische nauwkeurigheid van Excel sterk verbeterd (voor alle statistiek gerelateerde functies).

[Cerium]

Bedankt voor al je hulp! Ik heb trouwens ook nog een alternatieve manier gevonden om de coëfficiënten met een hoge nauwkeurigheid te pakken te krijgen. Ik werk trouwens met de NL versie van Excel 2007. Als je "rechtermuisklikt" op de vergelijking in de grafiek kan je naar opmaak van dat label gaan. Bij "getal" heb ik het aantal weergegeven decimalen verhoogd van 2 naar 30. Dan krijg je de coëfficiënten tot op machineprecisie.

[E.Desart]

Ik vergat iets extreem belangrijk .....

Als je de gewone Excel grafiek gebruikt geeft die formule op deze grafiek STEEDS een formule gebaseerd op X waarden van 1 tot n (aantal rijen in je tabel).

Dwz dat jij je formule moet uitbreiden met een conversie van je reële x waarden naar deze 1 tot n.

Om dit probleem te voorkomen moet je je grafiek als een x-y scatter grafiek zetten (geeft hetzelfde visueel resultaat). Op die manier houdt die formule WEL rekening met jouw juiste x-as waarden.

Via de functies krijg je hetzelfde resultaat als met de scatter grafiek.

Als je dit niet weet of vergeet kan je behoorlijk de mist ingaan.

PS: Een en ander kan iets anders werken nu in Excel 2007 waar ik geen ervaring mee heb (nog nooit geïnstalleerd)

[Extra Large]

Bedankt voor al je hulp! Ik heb trouwens ook nog een alternatieve manier gevonden om de coëfficiënten met een hoge nauwkeurigheid te pakken te krijgen. Ik werk trouwens met de NL versie van Excel 2007. Als je "rechtermuisklikt" op de vergelijking in de grafiek kan je naar opmaak van dat label gaan. Bij "getal" heb ik het aantal weergegeven decimalen verhoogd van 2 naar 30. Dan krijg je de coëfficiënten tot op machineprecisie.

Top! Nooit geweten. Ik heb hier al verschillende mensen naar laten kijken, maar nog nooit een antwoord op gehad. Ik worstel al jaren met dit vreemde fenomeen. Soms werkt de formule die Excel weergeeft wel en soms niet, precies zoals jij aangeeft. Net lukte het dus weer niet en ben ik gaan zoeken. Door jouw "truc" met de significante cijfers lukt het nu wel. Hartelijk bedankt.