Polynoom formule overnemen in Excel

[Copitano]

Mijn vraag
Ik heb een Excel spreadsheet met daarin van KNMI afgeleide zonnestraling in kWh/m² over een periode van 10 jaar. Daarvan bereken ik per dag van het jaar het gemiddelde, het maximum en het minimum. Die zet ik in een grafiek (plaatje is alleen december).
In de grafiek staan ook de gegevens van het lopende jaar. Door de gemiddelden heen zet ik een polynoom trendlijn voor max, min en gem. en toon daarvan ook de formule en de R².

Nu probeer ik die formule van de trendlijn in een werkblad te zetten langs de dagen van het jaar (X-as) om de waarde van de Y-as ter reproduceren uit de formule. Als ik daar dan weer een grafiek van maak krijg ik een plaatje wat in geen velden of wegen lijkt op de oorspronkelijke grafiek waar de formule uit 'geleend' is.
De formule in kolom B is

code:

= -0,00000000000009*A2^6 + 0,00000000009*A2^5 - 0,00000002*A2^4 - 0,000001*A2^3 + 0,0008*A2^2 - 0,0056*A2 + 1,2586

Ik heb dat ook al geprobeerd met gewoon de dag nummers van het jaar in kolom A, dus 1,2,3, enz.
Iemand enig idee waar het hier fout gaat?

Heb hier al wel wat heel oude (2007/2008) topics gevonden, maar daarmee kom ik niet veel verder.

[pedorus]

Je zit met massale afrondingsproblemen en een tool die eigenlijk niet geschikt is.
Normaal gesproken zou je eerst de exacte coëfficiënten kunnen bepalen met de volgende formule:

code:

=LINEST(y_1,x_1^{1,2,3,4,5,6})

waarbij we de namen x_1 en y_1 gedefinieerd hebben en de uitkomstcel coef noemen, en dan kun je met sumproduct een nieuwe waarde berekenen voor een cel A1:

code:

=SUMPRODUCT(A1^{6,5,4,3,2,1,0},coef#)

Maar dat gaat sinds versie 2013 niet meer omdat datums vrij hoge getallen zijn. Dus je moet eerst x_1 vernummeren. Als je toch de originele coëfficiënten wil weten, dan kan dat met:

code:

=MMULT(LINEST(y_1,(x_1-AVERAGE(x_1))^{1,2,3,4,5,6}),IFERROR(COMBIN({6;5;4;3;2;1;0},{6,5,4,3,2,1,0})*(-AVERAGE(x_1))^({6;5;4;3;2;1;0}-{6,5,4,3,2,1,0}),0))

En de waardes van de trend kun je ook voor hoge inputs berekenen met:

code:

=TREND(y_1,(x_1-AVERAGE(x_1))^{1,2,3,4,5,6})

En eventueel nieuwe waardes in x_2 met:

code:

=TREND(y_1,(x_1-AVERAGE(x_1))^{1,2,3,4,5,6},(x_2-AVERAGE(x_1))^{1,2,3,4,5,6})

En deze formules zijn in het Engels, aangezien je een Nederlandse versie en Nederlandse instellingen hebt zal je ze wellicht moeten vertalen.. En die snapt niet dat ; waarschijnlijk een \ moet worden. Niet eenvoudig.

[The Eagle]

Nou ben ik geen held met Excel, maar dit lijkt idd behoorlijk complex. Plus lastig in Excel te doen, wat er eigenlijk ook niet voor gemakt is. Is er een reden dat je dit niet in bijvoorbeeld R doet?

[g0tanks]

Welke data heb je geselecteerd voor de x-as in je grafiek? Wellicht dat je ook het bestand kan delen?

[Copitano]

pedorus schreef op vrijdag 18 december 2020 @ 22:19:
Je zit met massale afrondingsproblemen en een tool die eigenlijk niet geschikt is.
Normaal gesproken zou je eerst de exacte coëfficiënten kunnen bepalen met de volgende formule:

code:

=LINEST(y_1,x_1^{1,2,3,4,5,6})

waarbij we de namen x_1 en y_1 gedefinieerd hebben en de uitkomstcel coef noemen, en dan kun je met sumproduct een nieuwe waarde berekenen voor een cel A1:

code:

=SUMPRODUCT(A1^{6,5,4,3,2,1,0},coef#)

Maar dat gaat sinds versie 2013 niet meer omdat datums vrij hoge getallen zijn. Dus je moet eerst x_1 vernummeren. Als je toch de originele coëfficiënten wil weten, dan kan dat met:

code:

=MMULT(LINEST(y_1,(x_1-AVERAGE(x_1))^{1,2,3,4,5,6}),IFERROR(COMBIN({6;5;4;3;2;1;0},{6,5,4,3,2,1,0})*(-AVERAGE(x_1))^({6;5;4;3;2;1;0}-{6,5,4,3,2,1,0}),0))

En de waardes van de trend kun je ook voor hoge inputs berekenen met:

code:

=TREND(y_1,(x_1-AVERAGE(x_1))^{1,2,3,4,5,6})

En eventueel nieuwe waardes in x_2 met:

code:

=TREND(y_1,(x_1-AVERAGE(x_1))^{1,2,3,4,5,6},(x_2-AVERAGE(x_1))^{1,2,3,4,5,6})

En deze formules zijn in het Engels, aangezien je een Nederlandse versie en Nederlandse instellingen hebt zal je ze wellicht moeten vertalen.. En die snapt niet dat ; waarschijnlijk een \ moet worden. Niet eenvoudig.

toon volledige bericht

Hmmm, dit wordt een uitdaging maar die gaan we niet uit de weg.

[Copitano]

The Eagle schreef op vrijdag 18 december 2020 @ 22:34:
Nou ben ik geen held met Excel, maar dit lijkt idd behoorlijk complex. Plus lastig in Excel te doen, wat er eigenlijk ook niet voor gemakt is. Is er een reden dat je dit niet in bijvoorbeeld R doet?

Ik ben toevallig redelijk handig met Excel, vandaar Excel. En er kan heel veel met Excel, maar inderdaad soms zoek ik de randjes op van wat kan en ploft er wel eens een bestand

Wat is R? nooit van gehoord.

[Harrie]

Je kan normaal wel de cijfers in de polynoom formule met meer significante cijfers laten zien. Heb hetzelfde probleem ook eens gehad. Het is inderdaad doordat de coefficienten nu zwaar afgerond zijn volgens mij.

Edit, zie hier:
https://docs.microsoft.co...20significantly%20reduced.

[ Voor 41% gewijzigd door Harrie op 19-12-2020 11:52 ]

[Copitano]

g0tanks schreef op zaterdag 19 december 2020 @ 01:36:
Welke data heb je geselecteerd voor de x-as in je grafiek? Wellicht dat je ook het bestand kan delen?

Nu gebruik ik dag-maand voor de X-as, maar ik heb ook al gewoon 1,2,3 t/m 366 geprobeerd. Zelfde resultaat.
Wil het bestand best delen, maar moet dan eerst even uitzoeken hoe ik dat hier in het forum krijg. Het eerste plaatje onder is een draaitabel op de gegevens van weerstation Berkhout van het KNMI. Daar heb ik nog wat formules aan toegevoegd om J/cm² om te zetten naar kWh/m² en uit de datum alleen de dag en maand te halen zodat ik in de draaitabel alle jaren netjes naast elkaar kan zetten. Daar zet ik dan kolommen naast waarin ik over de jaren het max, min, en gemiddelde bereken. Het plaatje laat alleen december zien. Zou anders te groot worden. Maar jan t/m nov staan er uiteraard ook gewoon in.
De grafiek haal ik uit de draaitabel. X-as is dag-maand en Y-as kWh/m². In de grafiek zet ik dan een polynoomtrendlijn met volgorde 6. Dat fit perfect. De formule die daar dan bij hoort wil ik gebruiken om de polynoomlijn zelfstandig te reproduceren. Dat wil ik dan vervolgens weer gebruiken voor monitoring van de productie van mijn zonnepanelen. Het blijk namelijk (empirisch) dat de ratio tussen elektriciteitsproductie in kWh / kWh globale straling min of meer een constante is. Eigenlijk is dat ook wel logisch.
Waarom ik dat wil? Niet omdat het moet, maar omdat het kan gewoon voor de lol dus.

[Copitano]

Harrie schreef op zaterdag 19 december 2020 @ 11:42:
Je kan normaal wel de cijfers in de polynoom formule met meer significante cijfers laten zien. Heb hetzelfde probleem ook eens gehad. Het is inderdaad doordat de coefficienten nu zwaar afgerond zijn volgens mij.

Edit, zie hier:
https://docs.microsoft.co...20significantly%20reduced.

Eureka!

code:

y = -0,000000000000093881203908507000x6 + 0,000000000090234039369401100000x5 - 0,000000022458079053659800000000x4 - 0,000001411595046865720000000000x3 + 0,000799425038515267000000000000x2 - 0,005649781687679930000000000000x + 1,258582251588000000000000000000

Via kladbloik naar Excel

code:

 =-9,3881203908507E-14*A2^6 + 9,02340393694011E-11*A2^5 - 2,24580790536598E-08*A2^4 - 1,41159504686572E-06*A2^3 + 0,000799425038515267*A2^2 - 0,00564978168767993*A2 + 1,258582251588

En voilà

[ Voor 29% gewijzigd door Copitano op 19-12-2020 13:18 ]

[The Eagle]

Copitano schreef op zaterdag 19 december 2020 @ 11:42:
[...]

Ik ben toevallig redelijk handig met Excel, vandaar Excel. En er kan heel veel met Excel, maar inderdaad soms zoek ik de randjes op van wat kan en ploft er wel eens een bestand

Wat is R? nooit van gehoord.

https://www.r-project.org
R of Python is zo'n beetje de standaard bij de gemiddelde datascientist. Alles wat je nu moeilijk doet in Excel (wat eigenlijk in de basis voor boekhouders is gemaakt) knal je met een paar regels code in R.

[Copitano]

The Eagle schreef op zaterdag 19 december 2020 @ 20:39:
[...]

https://www.r-project.org
R of Python is zo'n beetje de standaard bij de gemiddelde datascientist. Alles wat je nu moeilijk doet in Excel (wat eigenlijk in de basis voor boekhouders is gemaakt) knal je met een paar regels code in R.

Ah, interessant. Ga dat zeker eens downloaden en uitproberen.