Hvordan beregne R2 Excel

Innen finans og statistikk er bestemmelseskoeffisient, også referert til som R-kvadrat (eller R²) er et mål på forholdet mellom to datasett som brukes i en matematisk modell. Den representerer forholdet mellom variansen i den avhengige variabelen som kan predikeres fra den uavhengige variabelen i modellen. Det brukes ofte i regresjonsanalyser for å evaluere spådommer om fremtidige utfall basert på observerte utfall. Du kan beregne R-kvadrat i Excel ved å bruke RSQ-funksjonen.

I Microsoft Excel brukes RSQ-funksjonen til å bestemme R-kvadratverdien for to sett med datapunkter. Funksjonen returnerer kvadratet av Pearson-produktmomentkorrelasjonskoeffisienten, som måler den lineære korrelasjonen mellom variablene x og y. Korrelasjonskoeffisienten faller alltid innenfor området -1 og +1. Verdien returnert av RSQ i Excel er alltid mellom 0 og 1 (siden den er beregnet som kvadratet av en korrelasjonskoeffisient, kan den aldri returnere en negativ verdi).

RSQ-funksjonen tar to datasett som argumenter, referert til som kjent_x og kjent_y. Disse datasettene kan være i form av en liste over tall eller en liste eller et utvalg av cellereferanser. Si for eksempel at du ønsket å gjøre en regresjonsanalyse av penger brukt på annonsering vs. inntekter fra salg, der månedlige annonseutgifter er oppført i kolonne A og månedlige inntekter er oppført i kolonne B. Du kan bruke RSQ-funksjonen ved å skrive inn RSQ(A1:A10,B1:B10), som bruker verdier i rad 1 til 10 fra kolonne A (annonsekostnader) og B (inntekt).

Excel gir også en måte å beregne korrelasjonskoeffisienten for to datasett ved å bruke funksjonene CORREL og PEARSON. I likhet med RSQ-funksjonen tar både CORREL og PEARSON to områder med celleverdier som argumenter. Å ta resultatet av CORREL eller PEARSON for å finne korrelasjonskoeffisienten og kvadrere resultatet er det samme som å bruke RSQ-funksjonen for å bestemme bestemmelseskoeffisienten.

CORREL- og PEARSON-funksjonene returnerer verdier mellom -1 og 1. Dette er et dimensjonsløst mål på positiv eller negativ korrelasjon mellom de to settene med data gitt som argumenter. Returverdien fra RSQ-funksjonen er mellom 0 og 1, noen ganger uttrykt som en prosentandel fra 0 til 100. Mange analytikere mener at et høyere RSQ-resultat indikerer en mer nøyaktig matematisk modell, mens andre si at det er viktig å se på alle faktorene som kan skjeve et høyt eller lavt resultat før du tegner konklusjoner.

Eksperter sier også at du bør unngå å sammenligne R-kvadrerte verdier for forskjellige modeller og sett med data. I tilfeller der det er store forskjeller mellom typen data som sammenlignes, kan resultatene være misvisende. Det er mer kompliserte tiltak for å sammenligne modeller enn R-kvadrerte verdier, som F-tester og informasjonskriterier.

Excel-spredningsdiagrammet brukes oftest for å vise sammenhenger mellom sett med data under regresjonsanalyser. Verdiområdet for ett sett med data vises på den horisontale x-aksen og området for det andre settet på den vertikale y-aksen. Datapunkter er kartlagt til skjæringspunktet mellom x- og y-verdier ved å bruke verdipar fra hvert datasett.

Ved å bruke reklame- og salgseksemplet, der annonsekostnadene er oppført i kolonne A og månedlig inntekt i kolonne B, den horisontale aksen vil vise rekkevidden av månedlig inntekt og den vertikale viser rekkevidden av annonsering kostnader. Datapunkter i diagrammet vil bli plottet ved å se på tilstøtende celler i kolonne A og kolonne B. Det resulterende mønsteret av poeng kan brukes til å visualisere mengden av korrelasjon mellom variablene.