Oplossen 3de graads vergelijking
Ano
y = a x^3 + b x^2 + c x +d
Zonder probleem kan ik y oplossen als ik x (cel A2) weet. Andersom
(oplossing y is bekend, ik wil x weten), lukt me me door handmatig de solver
functie te gebruiken.
Kan ik die solver ook automatisch laten werken, dus dat wanneer mijn waarde
voor y (cel A1) veranderd, hij zelf x gaat berekenen en die in de juiste cel
zet (A2)?
Bedankt!
Niek Otten
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"Ano" <a...@ano.invalid> wrote in message news:44e3759e$0$4530$e4fe...@news.xs4all.nl...
Ernest de Vroome
maar het kan ook "dirty", gewoon met de solver.
Zet in cel B1 de formule (als voorbeeld uiteraard):
=14*C1^3+15*C1^2+16*C1+17
En zet in C1 een vooralsnog willekeurig getal.
Nu maken we de relatief zeer eenvoudige macro:
Private Sub Worksheet_Change(ByVal Target As Range)
Range("B1").GoalSeek Goal:=Range("A1"), ChangingCell:=Range("C1")
End Sub
Dit stukje tekst moet dan wel op de goede plaats staan, hij moet zitten
'hangen aan' het blad waar bovenstaande formule in cel B1 staat.
Als je nooit met macro's werkt, klik dan met de rechtermuisknop op het
bewuste tabblad, en klik op "Programmacode weergeven". Knip/plak bovenstaande
macro naar het (waarschijnlijk volledig) lege veld dat je dan voor je hebt,
en sluit dat venster zoals je elk ander venster sluit.
Verander je nu cel A1, dan treedt zonder enige andere handeling de
doelzoeker in werking die het 'probleem' van het oplossen van zo'n derde (of
nog hogere!) graads vergelijking in minder dan een tiende seconde oplost.
Het kan dus ook met een combinatie van een zelfstartende macro en de
doelzoeker!
Met groet, Ernest de Vroome
"Niek Otten" schreef:
Ano
> http://people.stfx.ca/bliengme/ExcelTips/Cubic_Equation.htm
Ah, ja ik herinnerde me van de Universiteit dat er inderdaad een analytische
oplossing is voor een 3de graads, maar kan het nergens meer vinden (vanaf
4de graad is het een stuk ingewikkelder, geloof ik). Ik zal hem als extra
tabblad invoegen in mijn spreadsheet, bedankt!
Overigens wel vreemd dat er in die spreadsheet wordt gesproken over "cubic
equation" (dat is toch 2de graads?), maar dat mag de pret niet drukken.
Hoop kennis aanwezig in deze groep :-)
Ano
Bedankt voor je reactie, ik heb uiteindelijk toch voor jouw oplossing
gekozen, omdat de uiteindelijke formule (resultaat van een hele reeks
berekeningen), nogal complex is om om te schrijven naar een "nette"
ax^3+bx^2+cx+d :
=(E2*(0.992+0.0001055*E2-0.00000000967*E2*E2)+(0.622*D7/100*(IF((273+E2)<=37
3,EXP(-6729/(273+E2)-4.957*LN(273+E2)+0.000534*(273+E2)+53.821),760/1.013*(E
2/100)^4))/(760-D7/100*(IF((273+E2)<=373,EXP(-6729/(273+E2)-4.957*LN(273+E2)
+0.000534*(273+E2)+53.821),760/1.013*(E2/100)^4))))*(2500.3+1.825*E2+0.00030
4*E2*E2-0.00000000105*E2*E2*E2))*D5/((273+E2)*(0.00283+(0.622*D7/100*(IF((27
3+E2)<=373,EXP(-6729/(273+E2)-4.957*LN(273+E2)+0.000534*(273+E2)+53.821),760
/1.013*(E2/100)^4))/(760-D7/100*(IF((273+E2)<=373,EXP(-6729/(273+E2)-4.957*L
N(273+E2)+0.000534*(273+E2)+53.821),760/1.013*(E2/100)^4))))/219))
14*C1^3+15*C1^2+16*C1+17
(cel E2 is de variabele, overige celnummers die er nog in staan zijn
getallen)
Ik weet niet eens zeker meer of het wel een 3de macht is...
Maar bedankt, je oplossing werk perfect!
Niek Otten
Nee, dat is square. Je weet wel; een vierkante meter is tot de tweede en een kubieke meter tot de derde macht.
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"Ano" <a...@ano.invalid> wrote in message news:44e46323$0$4527$e4fe...@news.xs4all.nl...
Ano
> <"cubic equation" (dat is toch 2de graads?)>
>
> Nee, dat is square. Je weet wel; een vierkante meter is tot de tweede
> en een kubieke meter tot de derde macht.
ha ha ik moet toch echt eens vroeger naar bed gaan, schaam schaam...
Ano
> Ernest de Vroome wrote:
>> De algebraïsche oplossing waar Niek naar verwijst is het mooist,
>> admittedly, maar het kan ook "dirty", gewoon met de solver.
>>
>> Zet in cel B1 de formule (als voorbeeld uiteraard):
>> =14*C1^3+15*C1^2+16*C1+17
>> En zet in C1 een vooralsnog willekeurig getal.
Ik kwam nog een andere oplossing tegen.
Excel gaat zelf itereren zonder actief gebruik te maken van de Solver
functie, zolang onder Tools\Options een vinkje staat bij "Iteration" om
kringverwijzingen op te lossen (dat staat standaard uitgeschakeld).
Om aan te sluiten bij jouw voorbeeld:
In cel A1 staat de gewenste uitkomst van de formule.
In cel B1 staat de formule: =14*C1^3+15*C1^2+16*C1+17
In C1 zet ik een formule met een kringverwijzing: =IF(D1>0,C1+0.05,C1-0.05).
In cel D1 staat: =A1-B1 (dus het verschil tussen berekende en gewenste
uitkomst)
Excel gaat nu vanzelf itereren totdat in C1 de gewenste uitkomst staat!
Kenden jullie die truuk? Of kan Excel zo "vastlopen"?
Niek Otten
Ik ga natuurlijk niet zeggen dat Excel kan vastlopen, maar op zich is dit bedoeld gedrag; het wordt in diverse voorbeelden van
Microsoft zelf gebruikt.
Feitelijk is het zo dat in Excel iteratie en doelzoeken nogal door elkaar worden gehaald. Zo wordt bij doelzoeken het veld Maximum
aantal genegeerd als het beneden de standaardwaarde (100) komt.
Ook is volgens mij een kringverwijzing geen iteratie maar recursie (maar ik laat me graag door een wiskundige corrigeren).
Het werkt dus, niet getruukt, maar er zijn veel gevallen waar de doelzoek-optie transparanter is.
Als extraatje geef ik nog mijn standaardtekst over zelf-itererende functies, voor wie daar wat aan heeft.
' ===================================================================
Function Backward(ValueToBeFound As Double, MoreArguments As Double, _
Optional ReasonableGuess, Optional MaxNumberIters, _
Optional MaxDiffPerc) As Double
'
' Niek Otten, March 22 2006
'
' This EXAMPLE function goalseeks another function,
' called Forward. It works for almost any continuous function,
' although if that function has several maximum and/or minimum
' values, the value of the ReasonableGuess argument becomes
' important.
' It calculates the value for ReasonableGuess and for
' 1.2 * ReasonableGuess.
' It assumes that the function's graph is a straight line and
' extrapolates that line from these two values to find the value
' for the argument required to achieve ValueToBeFound.
' Of course that doesn't come out right, so it does it again for
' this new result and one of the other two results, depending on
' the required direction (greater or smaller).
' This process is repeated until the maximum number of calculations
' has been reached, in which case an errorvalue is returned,
' or until the value found is close enough, in which case
' the value of the most recently used argument is returned
Dim LowVar As Double, HighVar As Double, NowVar As Double
Dim LowResult As Double, HighResult As Double, NowResult As Double
Dim MaxDiff As Double
Dim NotReadyYet As Boolean
Dim IterCount As Long
If IsMissing(ReasonableGuess) Then ReasonableGuess = 1.5 ' use default Values
If IsMissing(MaxNumberIters) Then MaxNumberIters = 20 ' that make sense in the
If IsMissing(MaxDiffPerc) Then MaxDiffPerc = 0.001 ' context of the function
MaxDiff = ValueToBeFound * MaxDiffPerc
NotReadyYet = True
IterCount = 1
LowVar = ReasonableGuess
LowResult = Forward(LowVar, MoreArguments)
HighVar = LowVar * 1.2
HighResult = Forward(HighVar, MoreArguments)
While NotReadyYet
IterCount = IterCount + 1
If IterCount > MaxNumberIters Then
Backward = CVErr(xlErrValue) 'or some other errorvalue
Exit Function
End If
NowVar = ((ValueToBeFound - LowResult) * (HighVar - LowVar) + LowVar _
* (HighResult - LowResult)) / (HighResult - LowResult)
NowResult = Forward(NowVar, MoreArguments)
If NowResult > ValueToBeFound Then
HighVar = NowVar
HighResult = NowResult
Else
LowVar = NowVar
LowResult = NowResult
End If
If Abs(NowResult - ValueToBeFound) < MaxDiff Then NotReadyYet = False
Wend
Backward = NowVar
End Function
' ===================================================================
Function Forward(a As Double, b As Double) As Double
' This is just an example function;
' almost any continous function will work
Forward = 3 * a ^ (1.5) + b
End Function
' ===================================================================
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"Ano" <a...@ano.invalid> wrote in message news:44e4be6c$0$4518$e4fe...@news.xs4all.nl...
Ano
> <Kenden jullie die truuk? Of kan Excel zo "vastlopen"?>
>
> Ik ga natuurlijk niet zeggen dat Excel kan vastlopen, maar op zich is
> dit bedoeld gedrag; het wordt in diverse voorbeelden van Microsoft
> zelf gebruikt.
> Feitelijk is het zo dat in Excel iteratie en doelzoeken nogal door
> elkaar worden gehaald. Zo wordt bij doelzoeken het veld Maximum
> aantal genegeerd als het beneden de standaardwaarde (100) komt.
> Ook is volgens mij een kringverwijzing geen iteratie maar recursie
> (maar ik laat me graag door een wiskundige corrigeren).
>
> Het werkt dus, niet getruukt, maar er zijn veel gevallen waar de
> doelzoek-optie transparanter is.
>
> Als extraatje geef ik nog mijn standaardtekst over zelf-itererende
> functies, voor wie daar wat aan heeft.
<knip>
Ik kan het niet allemaal volgen, maar ik zit er wel mee dat er meerdere
oplossingen zijn van een functie hoger dan een 1ste graads, terwijl meestal
slechts 1 van de oplossingen een praktische betekenis heeft (als de functie
een proces uit de werkelijkheid beschrijft). Jij praat dan over een
"ReasonableGuess", geloof ik, om er voor te zorgen dat je de gewenste
oplossing vindt?
Ik gebruik nu jouw eerder genoemde macro [Private Sub Worksheet_Change(ByVal
Target As Range)
Range("B1").GoalSeek Goal:=Range("A1"), ChangingCell:=Range("C1")
End Sub]
Op de een of andere manier zou ik dan ook nog een "ReasonableGuess" als
input willen meegeven.
Hoe kan ik nu die macro van jou hiermee uitbreiden, zodat ik bij de juiste
oplossing terechtkom? Kan ik een deel van bovenstaande (weggeknipte) tekst
van jou toevoegen aan de macro? (NB Vandaag is de eerste keer dat ik een
macro heb ingevoerd, dus nog niet zoveel ervaring...)
Hartelijk bedankt voor je input tot zover!!
Ernest de Vroome
blijkt al gauw dat het a) erg onnauwkeurig is en b) wel erg 'dirty',
kringverwijzingen zijn in principe een check op programmeerfouten, niet om
vergelijkingen alsnog voor je op te lossen.
De 'best guess' voor de oplossing die in C1 komt te staan, is simpel wat er
op dat moment in C1 staat. Ik hou van zéér eenvoudige voorbeelden. Stel in B1
staat =C1^2 . In C1 staat 5. Voer je nu in cel A1 25 in, dan zegt de
doelzoeker 'klopt' en verandert niet. Voer nu in C1 het getal -5 in, A1
blijft op 25 staan. Ook als je in A1 nu bijv. 20 typt, zal in C1 een negatief
getal verschijnen, nl. -4,47. Type nu in cel C1 een willekeurig maar positief
getal in en +4,47 zal als oplossing worden gekozen door de doelzoeker,
aangezien dat dan de meest nabije oplossing is (in dit simpele voorbeeld zijn
er uiteraard altijd twee oplossingen).
Voer je echter min twee in in cel A1 dan gaat de doelzoeker door tot 100
iteraties of zoveel als je bij de opties hebt opgegeven en stopt dan
onverrichterzake.
Het mooiste is als je de startwaarde via een pop-up scherm aan de gebruiker
vraagt. De gehele macro wordt dan zo:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("C1") = Application.InputBox _
(Title:="© 2006 - Ernest de Vroome", _
Prompt:="What is your best guess?", Type:=1)
Range("B1").GoalSeek Goal:=Range("A1"), ChangingCell:=Range("C1")
Application.EnableEvents = True
End Sub
Bij © 2006 - Ernest de Vroome vul je gewoon je eigen naam in (die van mij
mag je ook laten staan ;-).
"Application.EnableEvents = False" dient om te voorkomen dat de macro
zichzelf start omdat hij zèlf eerst iets in cel C1 moet veranderen, en dan
kom je in een ongewenste loop. En na afloop zet je het weer 'aan'
(Application.EnableEvents = True).
De popup-vraag zou je eigenlijk voorwaardelijk moeten maken op een
verandering in A1. Na afloop zetten we de waarde van A1 telkens in A2:
Range("A2")=Range("A1"). De inputbox maken we conditioneel op het verschil
tussen A2 en A1. Dat geeft:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("A1") <> Range("A2") Then
Range("C1") = Application.InputBox _
(Title:="© 2006 - Ernest de Vroome", _
Prompt:="What is your best guess?", Type:=1)
Range("B1").GoalSeek Goal:=Range("A1"), ChangingCell:=Range("C1")
Range("A2") = Range("A1")
End If
Application.EnableEvents = True
End Sub
In deze 'discussie' wordt de doelzoeker soms 'oplosser' of 'solver' genoemd.
De 'oplosser'/'solver' is echter iets anders en is nog véél krachtiger dan de
doelzoeker, daarmee kun je vergelijkingen met meerdere 'variabelen' oplossen,
bijv. [B1]=C1^2+D1^2. Hij zit niet standaard onder de menu's, het is een
zogenaamde invoegtoepassing. (Extra... Invoegtoepassingen...
'Invoegtoepassing Oplosser' aanvinken etc. etc., ervanuitgaande dat je voor
de Nederlandse menuinstellingen e.d. hebt gekozen).
Met groet, Ernest
"Ano" schreef:
Niek Otten
blijkt al gauw dat het a) erg onnauwkeurig is en b) wel erg 'dirty',
kringverwijzingen zijn in principe een check op programmeerfouten, niet om
vergelijkingen alsnog voor je op te lossen.>
De nauwkeurigheid kun je zelf instellen.
Juist het (standaard) NIET toelaten van kringverwijzingen is een check op fouten. De iteratie-optie is speciaal gemaakt om (je
raadt het al) te itereren, althans in de terminologie van Excel.
Wat je met 'dirty' in dit verband bedoelt weet ik niet.
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"Ernest de Vroome" <Ernestd...@discussions.microsoft.com> wrote in message
news:2745E951-D215-4598...@microsoft.com...
Ernest de Vroome
Vanwaar toch al deze agressie?
Kom ik wellicht op een terrein met een bordje 'verboden toegang'?
'Normaal' (vanaf de Excel-fabriek) zijn kringverwijzingen 'eigenlijk' fout,
en dat is een hele prettige check op het in een cel onbedoeld verwjizen naar
die cel. Voorkeur heeft dan ook: Iteraties niet toestaan. Wil je toch
itereren, dan is het naar mijn bescheiden mening dan ook beter om dat
expliciet met bijvoorbeeld de doelzoeker en/of de oplosser te doen, en niet
door alsnog iteraties aan te zetten. Enkele tests wezen toch echt uit dat
iteraties onnauwkeurig zijn. Ja, tenzij je iteraties op 10.000 zet. Dan breng
ik het iets anders onder woorden, de doelzoeker is vele malen sneller dan
'passief' laten itereren via kringverwijzingen.
Wat bedoel ik met 'dirty'? Idealiter los je een vergelijking op door de
inverse te berekenen, of, bijvoorbeeld, maar dat komt in wezen op hetzelfde
neer, bijvoorbeeld de wortelformule toe te passen. Maar niet van alle
functies is de inverse bekend en/of is de inverse erg 'ingewikkeld'. Dan
passen we wat ik zo vrij ben om te noemen een 'dirty' methode toe, we gaan
(intelligent via afgeleiden of niet) net zo lang naar waarden zoeken tot we
een waarde vinden die de vergelijking oplost. In de statistiek is dat een
zeer veelgebruikte methode, gewoonlijk met als criterium dat de likelihood
wordt gemaximaliseerd. Toch is dat eigenlijk 'dirty'! Bijvoorbeeld LISREL
komt nogal eens met de foutmelding dat de 'convergentie' niet is gelukt, er
zijn dan in gewoon Nederlands een hele serie punten die allemaal dezelfde
likelihood hebben. Ook kan een lokaal minimum onbedoeld de 'eindoplossing'
worden. Zoeken en/of iterereren geeft tevens niet álle oplossingen
(bijvoorbeeld bij 25=x^2 alleen +5 en niet -5). Het oplossen van een
vergelijking door middel van een kringverwijzing, vind ik, met alle respect,
nóg 'dirtier', omdat je normaliter die kringverwijzing alléén gebruikt om
jezelf voor onbedoelde programmeerfouten te behoeden. Bij mij blijft het
hokje 'iteratie' bij de opties onder 'Berekenen' in ieder geval op uit staan,
en ik adviseer iedereen dat ook te doen. Wil je itereren, doe dat dan met
behulp van 'doelzoeken', dat is sneller en bij gelijk aantal iteraties
nauwkeuriger dan itereren via kringverwijzingen.
Voor het overige met vriendelijke groet, Ernest
"Niek Otten" schreef:
Niek Otten
<agressie?>
Ik weet niet waar je het over hebt!
<dan is het naar mijn bescheiden mening dan ook beter om dat expliciet met bijvoorbeeld de doelzoeker en/of de oplosser te doen>
Helemaal mee eens; precies wat ik enkele post geleden schreef: "er zijn veel gevallen waar de doelzoek-optie transparanter is"
Verder is kringverwijzing qua systematiek niet "dirtier" dat iteratie. Merk op (zoals ik al eerder aankaartte) dat Excel ten
onrechte de indruk wekt dat die twee hetzelfde zijn. Een kringverwijzing echter convergeert "vanzelf" (dat heeft zelfs enige
elegantie), terwijl dat bij doelzoeken niet hoeft (maar soms wel kan); meer brute force.
Zelf maak ik ook nooit gebruik van kringverwijzingen maar alleen van expliciete iteraties, maar dat was het punt niet.
Met vriendelijke groet,
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"Ernest de Vroome" <Ernestd...@discussions.microsoft.com> wrote in message
news:A50E4262-CDD1-4598...@microsoft.com...
Ano
Hoi Ernest,
Bedankt voor je uitgebreide reactie. Nu wil ik eigenlijk geen pop-up, maar
ik wil als beginwaarde voor C1 het resultaat gebruiken van een frormule in
een andere cel (A5).
Klopt het als ik het zo doe?
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("A1") <> Range("A2") Then
Range("C1") = A5
Range("B1").GoalSeek Goal:=Range("A1"), ChangingCell:=Range("C1")
Range("A2") = Range("A1")
End If
Application.EnableEvents = True
End Sub
Toch wel leuk, macro's!
Groet, Roland
Ano
Oh ja, dat gedeelte met "If Range("A1") <> Range("A2") Then..." kan dan weg
zeker?
Dan wordt het dus:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("C1") = A5
Range("B1").GoalSeek Goal:=Range("A1"), ChangingCell:=Range("C1")
Application.EnableEvents = True
End Sub
Dat "Application.EnableEvents = " doet nu ook zijn werk nog steeds goed?
Ernest de Vroome
bijna goed, Range("C1") = A5 zet in cel C1 de letterlijke tekst "A5", maar
je wilt natuurlijk de waarde van CEL A5. Dus:
Range("C1") = Range("A5")
Gelukkig heb ik VBA niet uitgevonden, want het is soms erg lastig.
De toets op veranderingen ("If Range("A1") <> Range("A2") Then..." ) is
absoluut niet verplicht o.i.d., maar voorkomt dat de macro wordt uitgevoerd
terwijl je dat helemaal niet wilt, als je heel iets anders in het 'blad' aan
het doen bent. Maar zonder 'InputBox' is dat aan de andere kant ook niet
storend. Macro's zijn wat dat betreft erg flexibel, het hoeft niet perfect te
zijn. Je kunt die 'Check' aan zetten maar ook uit laten.
Met groet, Ernest
"Ano" schreef:
> Ano wrote:
Ano
> Beste Ano,
>
> bijna goed, Range("C1") = A5 zet in cel C1 de letterlijke tekst "A5",
> maar je wilt natuurlijk de waarde van CEL A5. Dus:
> Range("C1") = Range("A5")
> Gelukkig heb ik VBA niet uitgevonden, want het is soms erg lastig.
> De toets op veranderingen ("If Range("A1") <> Range("A2") Then..." )
> is absoluut niet verplicht o.i.d., maar voorkomt dat de macro wordt
> uitgevoerd terwijl je dat helemaal niet wilt, als je heel iets anders
> in het 'blad' aan het doen bent. Maar zonder 'InputBox' is dat aan de
> andere kant ook niet storend. Macro's zijn wat dat betreft erg
> flexibel, het hoeft niet perfect te zijn. Je kunt die 'Check' aan
> zetten maar ook uit laten.
>
> Met groet, Ernest
Ah, dank je. Misschien dat "A5" i.p.v. Range("A5") de oorzaak ervan is dat
hij soms wel, soms niet goed werkt? Zie mijn nieuwe posting.