{"text_format":"3","workbook":"xls","n_sheets":12,"sheets":["Daten Beispiel 1","Koeffizienten Beispiel 1","Bestimmtheitsmass Beispiel 1","Anova Beispiel 1","Test Koeffizienten Beispiel 1","Vertrauensintervalle Beispiel 1","Daten Uebung 1","Koeffizienten Ue1","Anova UE1","Tests Koeffizienten UE1","Daten Uebung 2","Anova Tests für Koeff Ue2"],"not_rendered":{"external_links":1},"read":"whole","date_system":"1900","formulas":"cached_only"}

{"sheet":"Daten Beispiel 1","index":0,"n_rows":8,"n_cols":3,"rows_kept":8,"flags":["formulas_cached_only"]}
["Punkte","xi","yi"]
["<1, 4>",1,4]
["<2, 3>",2,3]
["<2.5 4>",2.5,4]
["<3, 4.9>",3,4.9]
["<7, 7.5>",7,7.5]
["<5, 5>",5,5]
["<6, 6>",6,6]

{"sheet":"Koeffizienten Beispiel 1","index":1,"n_rows":14,"n_cols":5,"rows_kept":14,"flags":["formulas_cached_only"]}
["Berechnung der Koeffizienten"]
["Punkte","xi","yi","xi*(yi-y)","xi(xi-x)"]
["<1, 4>",1,4,-0.914285714,-2.785714286]
["<2, 3>",2,3,-3.828571429,-3.571428571]
["<2.5 4>",2.5,4,-2.285714286,-3.214285714]
["<3, 4.9>",3,4.9,-0.042857143,-2.357142857]
["<7, 7.5>",7,7.5,18.1,22.5]
["<5, 5>",5,5,0.428571429,6.071428571]
["<6, 6>",6,6,6.514285714,13.28571429]
["Summen",null,null,17.97142857,29.92857143]
["Mittelwerte",3.785714286,4.914285714]
[]
[null,"Koeffizienten",null,"a =",0.6004773268925786]
[{"$nulls":3},"b =",2.641050119163673]

{"sheet":"Bestimmtheitsmass Beispiel 1","index":2,"n_rows":19,"n_cols":6,"rows_kept":19,"flags":["formulas_cached_only"]}
["Berechnung des Bestimheitsmasses"]
["Punkte","xi","yi","f(xi)","(f(xi)-y)^2","(yi-y)^2"]
["<1, 4>",1,4,3.2415274460562515,2.798120223930029,0.8359183673469377]
["<2, 3>",2,3,3.8420047729488305,1.1497864171543124,3.664489795918365]
["<2.5 4>",2.5,4,4.14224343639512,0.5960492788504972,0.8359183673469377]
["<3, 4.9>",3,4.9,4.442482099841409,0.22259865060271017,0.0002040816326530344]
["<7, 7.5>",7,7.5,6.844391407411724,3.7253079866374357,6.685918367346942]
["<5, 5>",5,5,5.643436753626566,0.5316612381718451,0.007346938775510304]
["<6, 6>",6,6,6.243914080519145,1.7679115922925845,1.1787755102040829]
["Mittelwerte",null,4.914285714285714]
["Summen",{"$nulls":3},10.791435387639414,13.20857142857143]
["Koeffizienten der Regressionsgleichung"]
["a =",0.6004773268925786,null,"Bestimmheitsmass:",null,0.8170024628323155]
["b =",2.641050119163673]
[]
[]
[null,null,"Mit Excelbefehl: ",null,null,0.8170024630402003]
[null,null,"Pearsonkorrelation:",null,null,0.9038818855581742]
[null,null,"Quadrat der Pearsonkorrelation",null,null,0.8170024630402003]

{"sheet":"Anova Beispiel 1","index":3,"n_rows":17,"n_cols":6,"rows_kept":17,"flags":["formulas_cached_only"]}
["ANOVA"]
["<xi, yi>","xi","yi","f(xi)","(yi-f(xi)^2","(f(xi)-y)^2"]
["<1, 4>",1,4,3.2415274460562515,0.5752806150859524,2.7981202229741693]
["<2, 3>",2,3,3.8420047729488305,0.7089720376686115,1.1497864165415819]
["<2.5 4>",2.5,4,4.14224343639512,0.02023319519749247,0.5960492784093311]
["<3, 4.9>",3,4.9,4.442482099841409,0.2093226289655268,0.22259865033310866]
["<7, 7.5>",7,7.5,6.844391407411724,0.4298226266755803,3.725307987740351]
["<5, 5>",5,5,5.643436753626566,0.4140108559174941,0.5316612385885019]
["<6, 6>",6,6,6.243914080519145,0.059494078675500106,1.7679115930523706]
["Summen",null,13.208571428571446,null,2.417136038186158,10.791435387639414]
["Mittelwerte",3.785714286,4.914285714]
[]
[null,null,"F-Wert:","((SQE)/(SQR/n-2))",22.322772109544587]
[{"$nulls":3},"P-Wert:",0.0052207120963441555,"<0.5"]
[]
["Koeffizienten","a =",0.6004773268925786]
[null,"b =",2.641050119163673]

{"sheet":"Test Koeffizienten Beispiel 1","index":4,"n_rows":18,"n_cols":8,"rows_kept":18,"flags":["formulas_cached_only"]}
["Berechung der T-Teststatistik für die Koeffizienten"]
[null,"xi","yi","f(xi)","yi-f(xi)","(xi-x)^2"]
["<1, 4>",1,4,3.2415274460562515,0.7584725539437485,7.76020408322449]
["<2, 3>",2,3,3.8420047729488305,-0.8420047729488305,3.1887755112244904]
["<2.5 4>",2.5,4,4.14224343639512,-0.14224343639511972,1.6530612252244903]
["<3, 4.9>",3,4.9,4.442482099841409,0.4575179001585914,0.61734693922449]
["<7, 7.5>",7,7.5,6.844391407411724,0.6556085925882762,10.331632651224488]
["<5, 5>",5,5,5.643436753626566,-0.6434367536265659,1.4744897952244895]
["<6, 6>",6,6,6.243914080519145,-0.24391408051914532,4.903061223224489]
["Summen",null,13.208571428571446,null,null,29.928571428571423]
["Mittelwerte",3.785714286,4.914285714]
[{"$nulls":3},"Varianz ei",0.48342720763723157]
["quad.Mittelwert",14.33163265522449]
[{"$nulls":3},"Für b:",null,null,"Für a:"]
["Koeffizienten","a =",0.6004773268925786,"Var(B)",0.3005555790074935,null,"Var(A)",0.01615269906186454]
[null,"b =",2.641050119163673,"Stand.fehler(B)",0.5482294948354143,null,"Stand.fehler(A)",0.12709326914461105]
[{"$nulls":3},"t-Wert:",4.81741705625771,null,"t-Wert:",4.724698097185108]
[{"$nulls":3},"p-Wert:",0.0024046720125073923,null,"p-Wert:",0.0026103560481720808]

{"sheet":"Vertrauensintervalle Beispiel 1","index":5,"n_rows":12,"n_cols":5,"rows_kept":12,"flags":["formulas_cached_only"]}
["Vertrauensintervalle"]
[]
["Koeffizienten","a =",0.6004773268925786]
[null,"b =",2.641050119163673]
["t-15(0.975)=",2.57058183469754]
[]
[]
["Für b:",null,null,"Für a:"]
["Var(B)",0.3005555790074935,null,"Var(A)",0.01615269906186454]
["Stand.fehler(B)",0.5482294948354143,null,"Stand.fehler(A)",0.12709326914461105]
["Untere Grenze",1.2317813384943481,null,null,0.27377367791711604]
["Obere Grenze",4.050318899832998,null,null,0.9271809758680412]

{"sheet":"Daten Uebung 1","index":6,"n_rows":11,"n_cols":3,"rows_kept":11,"flags":["formulas_cached_only"]}
["Objekt","Alter","Prämien"]
[1,60,350]
[2,30,540]
[3,20,570]
[4,35,490]
[5,45,450]
[6,44,450]
[7,28,550]
[8,64,360]
[9,18,600]
[10,54,400]

{"sheet":"Koeffizienten Ue1","index":7,"n_rows":19,"n_cols":5,"rows_kept":19,"flags":["formulas_cached_only"]}
["Berechnung der Koeffizienten"]
["Objekt","Alter","Prämien","xi(yi-y)","xi(xi-x)"]
[1,60,350,-7560,1212.0000000000002]
[2,30,540,1920,-293.9999999999999]
[3,20,570,1880,-395.99999999999994]
[4,35,490,490,-167.9999999999999]
[5,45,450,-1170,234.0000000000001]
[6,44,450,-1144,184.80000000000013]
[7,28,550,2072,-330.3999999999999]
[8,64,360,-7424,1548.8000000000002]
[9,18,600,2232,-392.4]
[10,54,400,-4104,766.8000000000002]
["Mittelwerte",39.8,476]
["Standardabweichungen",16.212478047616425,88.46845012017938]
["Summen",null,null,-12808,2365.6000000000013]
[]
[]
["Die Steigung ist somit: ",null,"a = ",-5.414271220831921]
["Der Ordinatenabschnitt: ",null,"b = ",691.4879945891105]

{"sheet":"Anova UE1","index":8,"n_rows":19,"n_cols":7,"rows_kept":19,"flags":["formulas_cached_only"]}
["Anova:"]
["Objekt","Alter","Prämien","f(xi)","(f(xi)-y)^2","(yi-f(xi)^2"]
[1,60,350,366.6317213391952,11961.420377227445,276.61415470464175]
[2,30,540,529.0598579641528,2815.348527176069,119.68670776451097]
[3,20,570,583.2025701724721,11492.391051583798,174.30785915904906]
[4,35,490,501.9885018599932,675.4022289268711,143.72417684706093]
[5,45,450,447.84578965167407,792.659560337783,4.6406222248345355]
[6,44,450,453.26006087250596,517.1048315221344,10.627996892444322]
[7,28,550,539.8884004058167,4081.727706413959,102.24444635308795]
[8,64,360,344.97463645586754,17167.645891872075,225.76154963334477]
[9,18,600,594.031112614136,13931.343544930845,35.62761662512681]
[10,54,400,399.11734866418675,5910.942076424227,0.779073380612915]
["Mittelwerte",39.8,476]
["Standardabweichungen",16.212478047616425,88.46845012017938]
["Summen",{"$nulls":3},69345.9857964152,1094.014203584714]
["Df",{"$nulls":3},1,8]
[null,"Koeffizienten der Regressionsgeraden"]
[null,"a=",-5.414271220831921,null,"F-Wert:",507.0938608964446]
[null,"b=",691.4879945891105,null,"p-Wert:",1.600989699716988e-08,"<0.05"]

{"sheet":"Tests Koeffizienten UE1","index":9,"n_rows":26,"n_cols":7,"rows_kept":26,"flags":["formulas_cached_only"]}
["T-Teststatistik für die Koeffizienten, Vertrauensintervalle"]
[]
["Objekt","Alter (xi)","Prämien (yi)","f(xi)","yi-f(xi)","(xi-xquer)^2"]
[1,60,350,366.6317213391952,-16.631721339195224,408.04000000000013]
[2,30,540,529.0598579641528,10.940142035847202,96.03999999999995]
[3,20,570,583.2025701724721,-13.202570172472065,392.0399999999999]
[4,35,490,501.9885018599932,-11.988501859993221,23.039999999999974]
[5,45,450,447.84578965167407,2.1542103483259325,27.04000000000003]
[6,44,450,453.26006087250596,-3.26006087250596,17.640000000000025]
[7,28,550,539.8884004058167,10.111599594183303,139.23999999999992]
[8,64,360,344.97463645586754,15.02536354413246,585.6400000000001]
[9,18,600,594.031112614136,5.968887385864036,475.2399999999999]
[10,54,400,399.11734866418675,0.8826513358132502,201.64000000000007]
["Mittelwerte",39.8,476]
["Standardabweichungen",16.212478047616425,88.46845012017938]
["Summen",{"$nulls":4},2365.6]
["Die Steigung a ist somit: ",null,-5.414271220831921,"Varianz ei",136.75177544808926]
["Der Ordinatenabschnitt: ",null,691.4879945891105,"T-Tests:"]
[{"$nulls":3},"Var(A)",0.0578084948630746,"Var(B)",105.24614574771358]
[{"$nulls":3},"S.Fehler(A)",0.24043397194047808,"S.Fehler(B)",10.258954417859238]
["T-18(0.975)=",2.3060041332991172,null,"t-Wert",-22.518744656317867,"t-Wert",67.40335968208785]
[{"$nulls":3},"p-Wert",8.00494849858494e-09,"p-Wert",1.3061257941570993e-12]
[]
[{"$nulls":3},"Vertrauensintervalle"]
[{"$nulls":3},"unter Grenze",-5.9687129539121875,null,667.8308032981998]
[{"$nulls":3},"obere Grenze",-4.859829487751655,null,715.1451858800211]

{"sheet":"Daten Uebung 2","index":10,"n_rows":13,"n_cols":3,"rows_kept":13,"flags":["formulas_cached_only"]}
["Laden","Verkaufsfläche (in 1000 qm)","Umsatz (in Mio Fr.)"]
[1,0.32,2.92]
[2,0.97,5.29]
[3,1.22,6.83]
[4,1.3,7.05]
[5,1.11,7.01]
[6,1.48,8.37]
[7,0.79,4.32]
[8,0.93,5.75]
[9,1.31,7.69]
[10,0.49,3.13]
[11,0.22,1.51]
[12,0.54,3.17]

{"sheet":"Anova Tests für Koeff Ue2","index":11,"n_rows":28,"n_cols":11,"rows_kept":28,"flags":["formulas_cached_only"]}
["Berechnung der Geradengleichung",{"$nulls":4},"Anova",null,null,"T-Test; Vertrauensintervalle"]
["Laden","Verkaufsfläche (in 1000 qm)","Umsatz (in Mio Fr.)","xi*(yi-y)","xi(xi-x)","f(xi)","(f(xi)-y)^2","(yi - f(xi))^2","yi-f(xi)","(xi-x)^2"]
[1,0.32,2.92,-0.7466666666666666,-0.1824000000000001,2.2797178048019897,8.84238931152274,0.40996128948758287,0.6402821951980102,0.3249000000000003]
[2,0.97,5.29,0.03556666666666706,0.07759999999999974,5.670682881197379,0.1741806451023239,0.14491945603673798,-0.38068288119737925,0.006399999999999958]
[3,1.22,6.83,1.9235333333333338,0.4025999999999997,6.97490021827253,2.9637925393192495,0.020996073255426732,-0.14490021827252964,0.10889999999999983]
[4,1.3,7.05,2.335666666666667,0.5329999999999998,7.392249766136578,4.574963506515758,0.11713490242054232,-0.342249766136578,0.16809999999999983]
[5,1.11,7.01,1.9499000000000004,0.24419999999999986,6.401044589959464,1.317241128586333,0.3708266914176369,0.6089554100405357,0.048399999999999936]
[6,1.48,8.37,4.612666666666666,0.8731999999999996,8.331286248830686,9.473794150018657,0.0014987545295994814,0.03871375116931297,0.3480999999999997]
[7,0.79,4.32,-0.7373333333333328,-0.07900000000000017,4.731646398503272,0.27215725797238455,0.16945275740071417,-0.41164639850327145,0.01000000000000004]
[8,0.93,5.75,0.46190000000000037,0.03719999999999983,5.462008107265356,0.043545161275580786,0.08293933028088288,0.2879918927346443,0.0015999999999999851]
[9,1.31,7.69,3.1920333333333346,0.5501999999999998,7.444418459619584,4.800854030632836,0.06031029297561812,0.24558154038041646,0.17639999999999983]
[10,0.49,3.13,-1.040433333333333,-0.19600000000000012,3.1665855940130916,4.354516127558138,0.0013385056892907708,-0.03658559401309169,0.1600000000000002]
[11,0.22,1.51,-0.8235333333333333,-0.14740000000000006,1.7580308699719298,12.217139310380293,0.061519312459032346,-0.2480308699719298,0.44890000000000035]
[12,0.54,3.17,-1.125,-0.1890000000000001,3.4274290614281218,3.3339264101616997,0.06626972166776372,-0.25742906142812183,0.12250000000000014]
["Mittelwerte",0.8900000000000002,5.253333333333333,{"$nulls":5},"Summe",1.9242]
["Summen",null,null,10.038300000000003,1.9241999999999977,null,52.368499579045995,1.5071670876208283,"Var(A)",0.07832694562004097]
[null,"Koeffizienten","a = ",5.2168693483006,null,"Freiheitsgrade",1,10,"S.Fehler(A)",0.2798695153460644]
[null,null,"b = ",0.6103196133457978,null,"F-Wert:",347.46313138852736,null,"t-Wert",18.640362962896596]
[{"$nulls":5},"p-Wert: ",4.26647412827289e-09,"< 0.05","p-Wert",2.133237064136445e-09]
[{"$nulls":8},"Var(B)",0.07460249935580805]
[{"$nulls":8},"S-Fehler (B)",0.27313458103251603]
[{"$nulls":8},"t-Wert",2.2345014352947885]
[{"$nulls":8},"p-Wert",0.02473172233475219]
[{"$nulls":8},0.15071670876208282,". = Var(ei)"]
[{"$nulls":7},"Vertrauensintervalle","für a",null,"für b"]
[{"$nulls":8},"Unter Grenze",4.593281210347131,0.0017378441375329734]
[{"$nulls":8},"Obere Grenze",5.840457486254069,1.2189013825540624]
[{"$nulls":8},"t-110(0.975)=",2.228138842425868]