Excel RSQ(): Vind de determinatiecoëfficiënt in Excel

Als je met trendlijnen werkt of wilt meten hoe sterk twee reeksen waarden samenhangen, is de functie RSQ() in Excel een eenvoudig hulpmiddel. RSQ() vertelt je hoeveel van de variatie in het ene bereik kan worden verklaard door het andere. Het geeft je in feite de R-kwadraatwaarde voor twee reeksen. Dit is handig voor alles van verkoopanalyses tot wetenschappelijke experimenten.

Wat doet RSQ()?

RSQ(), kort voor “R-kwadraat”, retourneert het kwadraat van de Pearson-correlatiecoëfficiënt tussen twee arrays, wat in dit geval ook de determinatiecoëfficiënt is. Simpel gezegd laat het zien hoe goed de waarden in het ene bereik de waarden in het andere voorspellen. Als het resultaat van RSQ() 1 is, heb je een perfecte lineaire relatie. Is het 0, dan is er helemaal geen lineaire relatie.

Mensen gebruiken RSQ() vaak wanneer ze willen controleren hoe goed een lijn bij hun data past, zoals bij regressieanalyse of bij het plotten van trendlijnen.

RSQ()-syntaxis en argumenten

Zo ziet de structuur van RSQ() eruit:

=RSQ(known_y’s, known_x’s)Mogelijk gemaakt door

• known_y’s: Dit zijn je afhankelijke waarden (de waarden die je probeert te voorspellen of te verklaren).

• known_x’s: Dit zijn je onafhankelijke waarden (de waarden waarvan je denkt dat ze de verklaring bieden).

Beide bereiken moeten hetzelfde aantal datapunten hebben. Als dat niet zo is, geeft RSQ() een foutmelding.

Belangrijke opmerking: Als je bekend bent met de ideeën achter eenvoudige lineaire regressie in het bijzonder en ordinary least squares lineaire regressie in het algemeen, zal je opvallen dat de functie RSQ() maar één set x-waarden accepteert. Dat komt omdat RSQ() geen meervoudige lineaire regressie ondersteunt.

Voorbeeld van RSQ()

Stel, je wilt weten hoe goed advertentie-uitgaven de verkoop voorspellen.

• Kolom A: Advertentie-uitgaven
• Kolom B: Verkoop

Stel dat je gegevens er zo uitzien:

Als je gegevens in A2:A5 voor advertenties en in B2:B5 voor verkoop staan, voer je in:

=RSQ(A2:A5, B2:B5)Mogelijk gemaakt door

Deze formule retourneert de R-kwadraatwaarde, die laat zien hoeveel van de verandering in verkoop kan worden verklaard door de verandering in advertentie-uitgaven.

RSQ()-resultaten interpreteren

• RSQ() = 1: Er is een perfecte lineaire relatie. De ene variabele voorspelt de andere zonder fout.
• RSQ() = 0: Er is geen lineaire relatie tussen je twee datasets.
• Waarden tussen 0 en 1: Hoe dichter bij 1, hoe sterker de lineaire relatie.

Als we in het bovenstaande voorbeeld zouden aannemen dat elk getal in kolom B het dubbele is van het overeenkomstige getal in kolom A, dan zou de regressie op een rechte lijn liggen en zou een verdubbeling van de advertentie-uitgaven de verkoop altijd verdubbelen. (Hoe mooi zou dat zijn!)

Veelgemaakte fouten

Hier zijn een paar problemen waar je met RSQ() tegenaan kunt lopen:

• Ongelijke bereikgroottes: Als je twee bereiken niet hetzelfde aantal rijen hebben, geeft RSQ() een #N/A-fout.

• Lege of niet-numerieke cellen: Lege cellen of tekst binnen de bereiken worden genegeerd, maar als er te veel ontbreken, kunnen de resultaten misleidend zijn of kunnen er fouten optreden. Dit kan een subtiele fout zijn, dus wees voorzichtig!

• Eén datapunt: RSQ() heeft minstens twee paren getallen nodig. Zo niet, dan krijg je een #DELEN/0!-fout. Dit komt omdat variantie/covariantie niet gedefinieerd is met slechts één waarde.

Als iets niet klopt, controleer je bereiken dan nog eens op typfouten of ontbrekende waarden.

RSQ() vs. CORREL()

Je vraagt je misschien af waarom je RSQ() zou gebruiken in plaats van CORREL(). Dit is het verschil:

• CORREL() retourneert de Pearson-correlatiecoëfficiënt (r), die de sterkte en richting van een lineaire relatie meet.

• RSQ() retourneert de R-kwadraat (R²), dus het geeft altijd een waarde tussen 0 en 1 en richt zich alleen op het aandeel verklaarde variantie.

Als je alleen wilt zien of je variabelen samen bewegen (positief of negatief), geeft CORREL() meer detail. Als je meet hoeveel van de verandering in de ene variabele wordt verklaard door een andere, dan heb je RSQ() nodig.

Dat gezegd hebbende: omdat we te maken hebben met eenvoudige lineaire regressie, kun je de R-kwadraat altijd vinden door het resultaat van CORREL() te kwadrateren:

=CORREL(A2:A5, B2:B5)^2Mogelijk gemaakt door

RSQ() en spreidingsdiagrammen

Als je ooit een trendlijn aan een spreidingsdiagram in Excel hebt toegevoegd, heb je misschien de R-kwadraatwaarde rechtstreeks op de grafiek zien staan. Dat is dezelfde waarde die RSQ() retourneert met je gegevensbereiken.

Wil je de R-kwadraatwaarde op je werkblad weergeven zonder een grafiek toe te voegen? Gebruik dan RSQ() rechtstreeks in een cel. Zo wordt het een getal waar je makkelijker mee kunt werken.

Als je niet 100% zeker weet hoe je echt goede visuals in Excel maakt, schrijf je dan in voor onze cursus Data Visualization in Excel, die alle belangrijke zaken in detail behandelt.

Praktische tips

Tot besluit van de technische uitleg, hier nog wat praktische tips:

• Label je data duidelijk om de X’en en Y’s uit elkaar te houden.

• Controleer altijd of je bereiken even groot zijn voordat je de formule invoert.

• Als je meer dan twee variabelen wilt vergelijken, overweeg dan regressieanalysetools zoals de Data Analysis Toolpak of de functie LINEST(). LINEST() rapporteert ook andere modelstatistieken, zoals de F-statistiek en standaardfouten.

Conclusie

Zoals je hebt gezien, is RSQ() een eenvoudige maar effectieve manier om te meten hoe goed de ene set waarden de andere in Excel voorspelt. Je kunt er veel mee doen, zoals trends analyseren of resultaten voorspellen. Laat het niet aan giswerk over.

Zet de volgende stap met onze cursussen Data Analysis in Excel en Advanced Excel Functions om je Excel- (en statistiek-)skills verder te verbeteren!