Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Excel berekend formule fout
900 views
Skip to first unread message
Hal
Aug 11, 2013, 7:23:28 AM8/11/13
to
Ik wil een formule afleiden uit een aantal concrete meetpunten, om zo
ontbrekende, tussenliggende waarden te berekenen. Ik heb de meetwaarden in
Excel gezet, een trendlijn (x^3) toegevoegd en de formule in de grafiek
geplaats (y = -6E-07x3 + 7E-06x2 - 0.0009x + 10.599).
Als ik die formule echter door Excel laat narekenen komt er niet de
startwaarde uit waarmee de trendlijn/formule gemaakt is: Ipv de verwachtte
10.6, komt Excel met 8.6. Of ik doe iets triviaals fout, of het licht aan de
instellingen: Ooit hielp een collega mij nl. met dit probleem. Had blijkbaar
iets te maken met precisie of aantal gebruikte factoren, add-on oid. Het is
echter te lang geleden om me te herinneren wat hij precies aan mijn
instellingen heeft gewijzigd.
(Wel slecht als het aan de instellingen licht, want Excel komt niet met een
melding)
Hal
ontbrekende, tussenliggende waarden te berekenen. Ik heb de meetwaarden in
Excel gezet, een trendlijn (x^3) toegevoegd en de formule in de grafiek
geplaats (y = -6E-07x3 + 7E-06x2 - 0.0009x + 10.599).
Als ik die formule echter door Excel laat narekenen komt er niet de
startwaarde uit waarmee de trendlijn/formule gemaakt is: Ipv de verwachtte
10.6, komt Excel met 8.6. Of ik doe iets triviaals fout, of het licht aan de
instellingen: Ooit hielp een collega mij nl. met dit probleem. Had blijkbaar
iets te maken met precisie of aantal gebruikte factoren, add-on oid. Het is
echter te lang geleden om me te herinneren wat hij precies aan mijn
instellingen heeft gewijzigd.
(Wel slecht als het aan de instellingen licht, want Excel komt niet met een
melding)
Hal
Hal
Aug 11, 2013, 7:42:19 AM8/11/13
to
>"Hal" schreef in bericht
>news:52077423$0$47896$c3e8da3$5d8f...@news.astraweb.com...
ik de formule op een ander blad in vul, klopt de uitkomst wél. Paste ik de
input naar dit tweede blad, gaat het weer fout.
Hal
Hal
Aug 11, 2013, 8:09:14 AM8/11/13
to
"Hal" schreef in bericht
news:5207788d$0$47912$c3e8da3$5d8f...@news.astraweb.com...
lijst gezet. Blijkbaar klopt dit wiskundig niet.
Maar nog kloppen de waarden niet. Blijkbaar rekent hij toch met andere
polynomen of zo. Daar lag volgens mij ook het probleem: het aantal cijfers
waarmee hij de polynomen berekend oid.
Hal
Alex Plantema
Aug 11, 2013, 9:11:08 AM8/11/13
to
Hal schreef in news:52077edd$0$47957$c3e8da3$5d8f...@news.astraweb.com
>>> (Wel slecht als het aan de instellingen licht, want Excel komt niet
>>> met een melding)
>
Zoals je hem hier geeft: -6E-07x3 + 7E-06x2 - 0.0009x + 10.599 zal het niet werken:
ontbrekende operatoren voor machtsverheffen en vermenigvuldigen, punten in plaats van komma's.
--
Alex.
>>> (Wel slecht als het aan de instellingen licht, want Excel komt niet
>>> met een melding)
>
>> Ik zie nu dat het inderdaad te maken moet hebben met instellingen,
>> want als ik de formule op een ander blad in vul, klopt de uitkomst
>> wél. Paste ik de input naar dit tweede blad, gaat het weer fout.
>
>> want als ik de formule op een ander blad in vul, klopt de uitkomst
>> wél. Paste ik de input naar dit tweede blad, gaat het weer fout.
>
> Al iets triviaals gevonden. Ik had de x-waarden in aflopende grootte
> in de lijst gezet. Blijkbaar klopt dit wiskundig niet.
> Maar nog kloppen de waarden niet. Blijkbaar rekent hij toch met andere
> polynomen of zo. Daar lag volgens mij ook het probleem: het aantal
> cijfers waarmee hij de polynomen berekend oid.
Als je de formule niet geeft waarmee je de waarden berekenT kunnen we niet zien waar het aan liGt.
> in de lijst gezet. Blijkbaar klopt dit wiskundig niet.
> Maar nog kloppen de waarden niet. Blijkbaar rekent hij toch met andere
> polynomen of zo. Daar lag volgens mij ook het probleem: het aantal
> cijfers waarmee hij de polynomen berekend oid.
Zoals je hem hier geeft: -6E-07x3 + 7E-06x2 - 0.0009x + 10.599 zal het niet werken:
ontbrekende operatoren voor machtsverheffen en vermenigvuldigen, punten in plaats van komma's.
--
Alex.
Hal
Aug 11, 2013, 9:46:05 AM8/11/13
to
"Alex Plantema" schreef in bericht
news:52078d69$0$16000$e4fe...@news2.news.xs4all.nl...
heb:
=0.0000005*D8^3 - 0.0003*D8^2 + 0.0414*D8 + 8.4034
De factoren zijn daardoor net wat anders.
Maar zoals gezegd, maar ik moet erg diep in mijn geheugen terug, ligt het
niet aan de formule zelf, maar aan de manier waarop Excel er mee om gaat.
Ergens op een plaats die je niet direct tegen het lijf loopt moet je iets
van 2 naar 4 digits instellen, meen ik me te herinneren.
Hierbij het hele sheetje:
http://www.mijnbestand.nl/Bestand-MJOZHGMQM783.xlsx
Doel is om de weerstandswaarden te vinden die ik nodig heb om een bepaalde
Utrip in te kunnen stellen. In de grafiek heb ik alleen de eerste twee
kolommen nodig.
Hal
Alex Plantema
Aug 11, 2013, 10:46:53 AM8/11/13
to
Hal schreef in news:52079590$0$47952$c3e8da3$5d8f...@news.astraweb.com
> Hier de formule nadat ik de getallen op de x-as van laag naar hoog
> gezet heb:
> =0.0000005*D8^3 - 0.0003*D8^2 + 0.0414*D8 + 8.4034
>
> De factoren zijn daardoor net wat anders.
> Maar zoals gezegd, maar ik moet erg diep in mijn geheugen terug, ligt
> het niet aan de formule zelf, maar aan de manier waarop Excel er mee
> om gaat. Ergens op een plaats die je niet direct tegen het lijf loopt
> moet je iets van 2 naar 4 digits instellen, meen ik me te herinneren.
> Hierbij het hele sheetje:
> http://www.mijnbestand.nl/Bestand-MJOZHGMQM783.xlsx
>
> Doel is om de weerstandswaarden te vinden die ik nodig heb om een
> bepaalde Utrip in te kunnen stellen. In de grafiek heb ik alleen de
> eerste twee kolommen nodig.
Kennelijk gaat het om de spanningsval van een spanningsbron met een inwendige weerstand bij belasting,
maar het blijft een onduidelijk verhaal: waarom je een derdegraadsfunctie gebruikt, waar je de coëfficienten uit afleidt
en hoe de schakeling precies in elkaar zit wordt uit het verhaal niet duidelijk.
Kennelijk staan de meetpunten in kolom E. Die worden niet in de formules gebruikt.
In kolom F wordt kennelijk de weerstand van een parallelschakeling berekend, in G de klemspanning.
Waar de formule in H vandaan komt begrijp ik niet. De waarde stijgt eerst en daalt later weer. Dat ziet er vreemd uit.
Het aantal decimalen dat je instelt heeft geen invloed op de berekeningen.
--
Alex.
> Hier de formule nadat ik de getallen op de x-as van laag naar hoog
> gezet heb:
> =0.0000005*D8^3 - 0.0003*D8^2 + 0.0414*D8 + 8.4034
>
> De factoren zijn daardoor net wat anders.
> Maar zoals gezegd, maar ik moet erg diep in mijn geheugen terug, ligt
> het niet aan de formule zelf, maar aan de manier waarop Excel er mee
> om gaat. Ergens op een plaats die je niet direct tegen het lijf loopt
> moet je iets van 2 naar 4 digits instellen, meen ik me te herinneren.
> Hierbij het hele sheetje:
> http://www.mijnbestand.nl/Bestand-MJOZHGMQM783.xlsx
>
> Doel is om de weerstandswaarden te vinden die ik nodig heb om een
> bepaalde Utrip in te kunnen stellen. In de grafiek heb ik alleen de
> eerste twee kolommen nodig.
maar het blijft een onduidelijk verhaal: waarom je een derdegraadsfunctie gebruikt, waar je de coëfficienten uit afleidt
en hoe de schakeling precies in elkaar zit wordt uit het verhaal niet duidelijk.
Kennelijk staan de meetpunten in kolom E. Die worden niet in de formules gebruikt.
In kolom F wordt kennelijk de weerstand van een parallelschakeling berekend, in G de klemspanning.
Waar de formule in H vandaan komt begrijp ik niet. De waarde stijgt eerst en daalt later weer. Dat ziet er vreemd uit.
Het aantal decimalen dat je instelt heeft geen invloed op de berekeningen.
--
Alex.
Hal
Aug 11, 2013, 3:20:01 PM8/11/13
to
"Alex Plantema" schreef in bericht
meer uitleggen. Ik stel je hulp nl. zeker wel op prijs. Het wordt echter wel
een heel verhaal.
(Eigenlijk zou de laatste alinea alle info moeten geven)
Ik gebruik een accuutje van 22 Ah voor het aansturen van een klein
waterpompje dat zo'n 5.5 A trekt. Omdat je een accu niet te diep moet
ontladen heb ik een schakelingetje gekocht dat de accu afschakelt als de
spanning te ver zakt. De afschakelspanning heb ik Utrip genoemd. Wat blijkt
nu. Omdat het een versleten accu is met een tamelijk hoger Ri. zakt de
spanning tijdens bedrijf veel verder dan de origineel ingestelde Utrip van
11.2 V. Dat die klemspanning zo ver zakt wil nog niet zeggen dat de accu
diep ontladen is. Om dat te bepalen moet je de accu lang genoeg onbelast
laten staan en pas die gemeten spanning bepaalt of de accu ver ontladen is.
M.a.w., het circuit schakelt mijn accu af op basis van de lage klemspanning
en niet op basis van diepe ontlading. Natuurlijk komt dit omdat mijn accu te
licht is voor deze applicatie, maar iets anders heb ik nog niet. Ik moet
die accu ook dagelijks naar de plaats van bestemming brengen, dus een zware
lood accu stel ik ook niet echt op prijs.
Nu blijkt Conrad ook zo'n circuit te leveren (weliswaar onder een andere
naam) en zelfs daarbij het schema.
(http://www.brigatti.nl/contents/nl/d455.html#p2540) Het is een soort
flipflop met de LM339 (Voor zover je elektronisch onderlegd bent). Ik heb de
ingangsweerstand opgezocht die de Utrip bepaalt en die Utrip bij
verschillende weerstandswaarden bepaald. Zo kan ik met een schakelaar straks
verschillen Utrips selecteren, al naargelang de situatie vereist. (Niet
ideaal, een zware accu ware beter.)
Dat zie je in de eerste twee kolommen. Die andere kolommen G en H gebruikte
ik om te kijken hoe ik verder moest. Of ik kon beredeneren/berekenen hoe het
verband tussen die weerstand en Utrip is. Dat heb ik echter opgegeven. Die
doen dus verder in het verhaal niet mee.
Nu heeft Excel, naast nog een paar andere leuke mogelijkheden (oplossen van
twee vergelijkingen met twee onbekende bv.), de mogelijkheid om van een
toegevoegde trendlijn de functie te bepalen. Dat heb ik gedaan. Waarom een
derdegraads functie: simpelweg omdat die het beste door mijn meetpunten
ging. Ik weet immers niet welke functie er in het circuit zit ingebouwd, dus
pak ik de beste fit, maar wiskunde was nooit mijn sterkste kant.
Die functie heb ik in kolom H uitgezet.
Kolom E met de gemeten waarden en kolom H, ter controle, met de berekende
waarden. Die twee moeten dus overeenkomen en zoals je ziet doen ze dat in de
verste verte niet.
Oorzaak zit hem in het feit, weet ik nu, dat Excel de formule van de
trendlijn verkeerd weer geeft. Dit komt door de al eerder vermelde
instelling, die ergens in Excel zit. Ik dacht dat dit te maken had met het
feit dat er ergens 2 ipv minimaal vier digits staan ingesteld. Dat is dus
iets anders dan het aantal decimalen dat Excel bij berekeningen gebruikt.
Dat zal echt wel snor zitten.
Natuurlijk kan ik uit de grafiek de waarden aflezen die ik nodig heb, maar
ik dacht, laat ik eens proberen die netjes te bereken. Ik moet de geest ook
soepel houden :-)
Dus nog even samengevat: Je stelt, "waar die formule vandaan komt begrijp ik
niet". Wel, die formule is door Excel gegeven.
Mocht je de functie niet kennen: als je een trendlijn toevoegt kun je die
ook "opmaken". In het opmaakmenu kun je een vinkje zetten bij: "Vergelijking
in de grafiek weergeven" (evenals de fout R2). Excel geeft je dan de
vergelijking van die trendlijn. Die vergelijking is dus gewoon fout en ik
zoek de plaats om die instelling aan te passen.
Hal
Hal
Aug 11, 2013, 3:29:13 PM8/11/13
to
"Alex Plantema" schreef in bericht
http://www.mijnbestand.nl/Bestand-LCAMEE4AYTEC.pdf
Ik sleutel dus op dit moment aan R3
Hal
Aug 11, 2013, 3:46:02 PM8/11/13
to
En hier iemand die met hetzelfde probleem zit (maar volgens mij geen
oplossing. die is er nl. wel.)
http://www.helpmij.nl/forum/showthread.php/457823-vergelijking-trendlijn.
oplossing. die is er nl. wel.)
http://www.helpmij.nl/forum/showthread.php/457823-vergelijking-trendlijn.
Hal
Aug 11, 2013, 5:48:19 PM8/11/13
to
Hier iets meer info over het probleem:
http://www.wetenschapsforum.nl/index.php/topic/127064-curve-fitting-in-excel-2007/,
maar het lukt me nog niet dit op te lossen.
http://www.wetenschapsforum.nl/index.php/topic/127064-curve-fitting-in-excel-2007/,
maar het lukt me nog niet dit op te lossen.
Hal
Aug 12, 2013, 7:11:29 AM8/12/13
to
"Alex Plantema" schreef in bericht
eigenlijk niets fout. Alleen wat onhandig voor de argeloze gebruiker: Excel
geeft de formule met te weinig decimalen weer. Als je dan met deze afgeronde
factoren gaat rekenen, verminken de afrondingsfouten het hele verhaal.
Hoe kwam ik daar achter: Gewoon met het handje en wat geduld de factoren net
zo lang aanpassen, tot de fit wél klopt. Dan zie je dat alle factoren
afgerond zijn.
Door Excel weergegeven formule: 5E-07x3 - 0.0003x2 + 0.0414x + 8.4034
De door mij gefitte formule: 5.385E-07x3 - 0.000259x2 + 0.041x + 8.45
Met deze uitleg zie je dus dat ik niet goed gefit heb. Ik mag de factoren
alleen zodanig wijzigen, dat als ze worden afgerond, de oorspronkelijke
getallen overblijven. Vanuit die optiek heb ik het nog niet geprobeerd.
Laatste stukje van de puzzel is dus uit te vinden waar ik in Excel kan
aangeven dat ik meer decimalen gedisplayed wil zien.
En dat heb ik ondertussen gevonden (http://support.microsoft.com/kb/282135).
Zoals ik in het begin al stelde, niet zo gemakkelijk te vinden. Pas als je
weet wat er aan de hand is en waar je op moet searchen wordt het eenvoudig.
Even wat veel digits, maar de formule wordt dus:
y = 0.000000534567753x3 - 0.000258458802872x2 + 0.041387657528588x +
8.403382643268740
Met 17 !!! decimalen heb ik maximale nauwkeurigheid.
Klein detail: De door mij gefitte kurve past beter dan de formule van Excel,
maar een kniesoor die daar op let -:). Misschien veroorzaakt door het feit
dat Excel standaard met 15 digits nauwkeurigheid rekent en hier 19 digits
nodig zijn?
Of een andere oorzaak?
Hal
Alex Plantema
Aug 12, 2013, 12:34:47 PM8/12/13
to
Ik heb het in Office 2010 geprobeerd. In de help gezocht naar trend en daar vond ik de tip:
"De vergelijking van de trendlijn wordt afgerond zodat deze beter leesbaar is. U kunt echter het aantal cijfers achter de komma voor een geselecteerd trendlijnlabel opgeven in het vak Decimalen op het tabblad Getal van het dialoogvenster Trendlijnlabel opmaken. (Tabblad Opmaak, groep Huidige selectie, knop Selectie opmaken)."
Maar het tabblad Getal zag ik niet, en het vak Decimalen dus ook niet. En het dialoogvenster heet Trendlijn opmaken.
Toen maar alle meetwaarden met 1000 vermenigvuldigd, dan krijg je ook meer cijfers in de formule.
De coëfficienten deel je dan weer door 1000. Dat geeft:
=((0,0000005*d8-0,0002569)*D8+0,040872)*D8+8,4445
Ze zijn iets anders dan eerst, maar de R-kwadraat is hetzelfde: 0,9994.
Omdat de coëfficient van de derde macht zo klein is kun je ook wel een kwadratisch polynoom kiezen. Dat geeft:
=(-0,0001131*D8+0,029574)*D8+8,7011
Beide grafieken gaan netjes door de meetpunten. Alleen aan het eind gaan ze iets omlaag, wat niet hoort, maar het meten gebeurt maar met beperkte nauwkeurigheid.
--
Alex.
"De vergelijking van de trendlijn wordt afgerond zodat deze beter leesbaar is. U kunt echter het aantal cijfers achter de komma voor een geselecteerd trendlijnlabel opgeven in het vak Decimalen op het tabblad Getal van het dialoogvenster Trendlijnlabel opmaken. (Tabblad Opmaak, groep Huidige selectie, knop Selectie opmaken)."
Maar het tabblad Getal zag ik niet, en het vak Decimalen dus ook niet. En het dialoogvenster heet Trendlijn opmaken.
Toen maar alle meetwaarden met 1000 vermenigvuldigd, dan krijg je ook meer cijfers in de formule.
De coëfficienten deel je dan weer door 1000. Dat geeft:
=((0,0000005*d8-0,0002569)*D8+0,040872)*D8+8,4445
Ze zijn iets anders dan eerst, maar de R-kwadraat is hetzelfde: 0,9994.
Omdat de coëfficient van de derde macht zo klein is kun je ook wel een kwadratisch polynoom kiezen. Dat geeft:
=(-0,0001131*D8+0,029574)*D8+8,7011
Beide grafieken gaan netjes door de meetpunten. Alleen aan het eind gaan ze iets omlaag, wat niet hoort, maar het meten gebeurt maar met beperkte nauwkeurigheid.
--
Alex.
Hal
Aug 12, 2013, 4:04:55 PM8/12/13
to
>"Alex Plantema" schreef in bericht
Bij mij is de locatie van de instelling niet 'n tabblad, maar het woordje
"getal" in de lijst van het pop-up venster. Kom daar maar eens op.
Het meten gebeurt inderdaad maar met beperkte nauwkeurigheid, maar de
trendlijn is wel de basis voor de formule. In mijn geval zou ik dus
verwachten dat ze echt exact op elkaar zouden liggen. Dat is dus niet.
In elk geval ben ik er uit en snap ik hoe het zit. Nu weer over tot de orde
van de dag en mijn circuitje af maken.
Gr. Hal
zalhet...@gmail.com
Apr 18, 2014, 8:15:30 AM4/18/14
to
Je bent een HELD!!!!!
0 new messages