lineaire regressie berekenen met de functie LIJNSCH

[Woffels]

Ik wil van een flinke dataset de a en Rkwadraat van deze punten bepalen van lineaire regressie lijn y = ax

Nu bestaat de formule LIJNSCH om dit perfect mee te berekenen en die werkt prima bij een aaneengesloten data set waarbij er geen getallen ontbreken.
Linker tabel in de bijlage
Haal ik nu één cijfer weg uit de dataset, werkt LIJNSCH niet meer en komt ie met een error. Rechter tabel.
Ook de Rkwadraat komt niet overeen zoals die in de grafiek staat.
Is dit op te lossen, het gaat om een hele grote dataset waar ik de lege cellen niet uit wil halen door rijen te verwijderen.

 

[AlexCEL]

Haal ik nu één cijfer weg uit de dataset, werkt LIJNSCH niet meer

Optie?

=LIJNSCH(FILTER(G3:G7;G3:G7<>"";);FILTER(H3:H7;G3:G7<>"";))

Ook de Rkwadraat komt niet overeen zoals die in de grafiek staat.

Klik in de grafiek met de rechtermuisknop de trendlijn en kies "trendlijn opmaken". Haal het vinkje weg bij "snijpunt instellen" en de R^2 waarden komen overeen.

 

[Woffels]

Je oplossing van het eerste probleem werkt perfect, alleen die Rkwadraat heb ik mss niet goed uitgelegd. Die grafiek met trendlijn heb ik erbij gedaan om te laten zien wat de Rkwadraat in de gele cel werkelijk moet zijn als je voor een y = ax (zonder asafsnede) regressie lijn kiest. In dit geval zou het getal in de gele cel 0,9852 moeten zijn, maar hij geeft een Rkwadraat van 0,920454545 in die cel wat staat voor een Rkwadraat als je die zou berekenen voor een regressielijn met de formule y = ax + b (met asafsnede).

 

[Woffels]

Ik heb nog even zitten spelen met de waarden in de tabel en de oplossing van #2 toegepast in de blauwe cel. De formule werkt prima als er een waarde ontbreekt in de eerste kolom van dat tabelletje, maar het kan ook voorkomen dat in die tweede kolom een waarde ontbreekt en dan krijg ik weer een foutmelding.
Ik heb zelf een formule gemaakt met filter (groene cel), maar dan krijg ik niet de juiste uitkomst in die cel. Wat doe ik niet goed?

=LIJNSCH(FILTER(G3:H7;(G3:G7<>"")*(H3:H7<>"")))

 

[AlexCEL]

Goed op weg met bovenstaande formule, maar je moet een onderdeel 2x gebruiken (om de x'jes en de y'tjes aan te geven) anders kan LIJNSCH er niet mee overweg. Dus zo:

=LIJNSCH(FILTER(G3:G7;(G3:G7<>"")*(H3:H7<>""););FILTER(H3:H7;(G3:G7<>"")*(H3:H7<>"");))

Je kan hier trouwens hele kunstwerkjes van maken in Excel 365...

=LET(g;G3:G7;h;H3:H7;v;(g<>"")*(h<>"");LIJNSCH(FILTER(g;v);FILTER(h;v)))

Over de R2. Welke waarde wil je zien? Als ik speur op internet kom ik meerdere artikelen tegen over dit onderwerp, dat er verschil zit in de trendlijn in de grafiek en een berekende waarde.

 

[Woffels]

In de bijlage een voorbeeld met een iets grotere dataset om eea duidelijker te maken.
In de grafiek zie je wat excel met de dataset doet als ik er een spreidingsgrafiek van maak. Bij de instellingen van de grafiek kan je 2 soorten trendlijnen toevoegen en de formule bepalen en de R-kwadraat laten berekenen voor beide soorten.

De rode trendlijn in de grafiek kent de formule y = ax, waarbij ie de lijn door punt {0,0} forceert. In dit geval maakt ie de formule y = 1,2008x met een Rkwadraat van 0.8678, zoals je dat in de grafiek ziet.
Met de LIJNSCH formule (D3) kan je de a en b laten berekenen, waarbij met parameter ONWAAR je aangeeft dat het een y = ax lijn moet zijn in de berekening. b = dus in dit geval 0

=LIJNSCH(B2:B374;A2:A374;ONWAAR)

De zwarte trendlijn kent de formule y = ax + b, waarbij ie de lijn een asafsnede geeft. In dit geval maakt ie de formule y = 0.9194x + 3.6101 met een Rkwadraat van 0.7194, zoals je dat in de grafiek ziet.
Met de LIJNSCH formule (D4) kan je de a en b laten berekenen, door nu ONWAAR weg te laten geef je aan dat het een y = ax + b lijn moet zijn in de berekening. b, de asafsnede is dus in dit geval 3.6101.

=LIJNSCH(B2:B374;A2:A374)

In cel f4 bereken ik de Rkwadraat (en komt ook overeen met de waarde in de grafiek) van deze y = ax + b lijn met:

=R.KWADRAAT(B2:B374;A2:A374)

Nu wil ik in cel F3 de Rkwadraat van de y = ax lijn berekenen (die volgens de grafiek 0.8678 zou moeten zijn), maar dat gaat niet met de formule R.KWADRAAT, die is gebaseerd op een y = ax + b lijn.

 

[cow18]

dit werkt in een 2021-365, anders moet je misschien matrixformules gebruiken (CTRL+SHIFT+ENTER) en het gebruik van de 4e parameter van Lijnsch.
Het is voor serieuze berekeningen nog altijd beter een echt statistisch pakket te gebruiken ipv. excel
Nu ben ik er al eventjes uit kwa statistiek, maar de beste regressierechte geeft de beste R2 en hier is de R2 van b=0 > R2 van b<>0, dus zou hij toch die van b=0 moeten kiezen.
Voor zover mijn kennis statistiek nog up-to-date is ...

 

[Woffels]

Die zocht ik, bedankt. Toch nog een vraagje, maar dat gaat meer om de opmaak. Is het mogelijk de uitkomsten uit die tabel horizontaal achter elkaar te krijgen in een regel in de plaats van in een kleine tabel?

 

[cow18]

tja, ofwel met die index die ik al gebruikt had voor R2 ofwel een klein UDF-je die het in een array gooit (die doet maar 1 keer de berekeningen).
Opnieuw : misschien heb je matrixformules nodig afhankelijk van je versie

 

[Woffels]

Werkt voor mij prima zo, bedankt.

 

[cow18]

je kan natuurlijk de naam van die UDF veranderen naar iets geschikter, iets die beter de lading dekt en je kan ook nog die 3e en 4e parameter gaan meegebruiken, zodat je kan kiezen tussen wel/niet door de nul en wel/niet de volledige reeks cijfers