'#Reference {C9E29040-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E2903D-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29039-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29044-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29043-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29042-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {04B00066-9D98-4C04-8BAF-5E1178BAEEE6}#1.0#0#
'#Reference {C9E29037-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29038-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29017-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29032-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29036-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29035-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29031-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29034-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E2902E-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E2902A-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E2902D-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29030-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E2902B-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29029-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29028-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E2903F-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29024-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E2902C-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29022-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29010-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29020-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29005-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29006-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29008-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E2900F-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29012-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29015-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29016-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29021-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29018-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29019-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E2901A-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E2901B-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E2901C-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E2901D-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29014-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29013-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29011-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E2900D-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E2900C-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E2900A-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29009-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29004-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29007-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E2900E-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E2900B-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29001-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {BE35B7EB-0D00-44D8-B3A5-0727DF5AD7C1}#1.0#0#
'#Reference {C9E29025-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29003-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
'#Reference {C9E29002-3D45-11D4-9FF4-00C04FA0D540}#1.0#0#
Option Base 1

Sub Main

	Dim alpha As Double 'hladina vyznamnosti
	Dim mean As Double 'priemer
	Dim stdev As Double 'vyberova smerodatna odchylka
	Dim C As Double 'ocakavana hodnota rozptylu
	Dim alt As Double 'typ alternativnej hypotezy
	Dim Matrix() As Double 'matica nameranych hodnot
	Dim i As Double 'forcyklovske premenne
	Dim pom1 As Double 'pomocna premenna vo for cykle vypoctu vyberoveho rozptylu
	Dim VarList () As Long 'zoznam vybranych premennych vyberu
	Dim pmen As Double 'P(T0 <= t0)
	Dim pvac As Double 'P(T0 >= t0)
	Dim K As Double 'hodnota testovej statistiky

	Const SelOneVar As String = "Zvolte jednu premennú"
	Const SelVar As String = "Výber premennej"
	Const SelTypeOfAltHyp As String = "Zvolte typ alternatívnej hypotézy"
	Const TypeOfAltHyp As String = "Obojstranná alternatíva|Lavostranná alternatíva|Pravostranná alternatíva"
	Const SprHead As String = "Test of Variance Against Reference Constant"
	Const SprC1 As String = "Summary"
	Const SprV1 As String = "Mean"
	Const SprV2 As String = "St. Dev."
	Const SprV3 As String = "K"
	Const SprV4 As String = "Chi_alpha/2 (n-1)"
	Const SprV5 As String = "Chi_1-alpha/2 (n-1)"
	Const SprV6 As String = "Chi_alpha (n-1)"
	Const SprV7 As String = "Chi_1-alpha (n-1)"
	Const SprV8 As String = "p-value"

	numvar=ActiveSpreadsheet.NumberOfVariables
	numcas=ActiveSpreadsheet.NumberOfCases

	ReDim VarList(1 To numvar)
	ReDim Preserve Matrix(numcas,numvar) As Double
	Matrix = ActiveSpreadsheet.Data

	'Volba premennej
	If 0=SelectVariables1(ActiveDataSet,SelVar,1,1,VarList,Count,SelOneVar) Then
		End
	End If

	LevOfSign=InputBox("Zadajte hladinu významnosti, na ktorej bude testovaná hypotéza:","Hladina významnosti")
	alpha=CDbl(LevOfSign) 'prevod stringu na Double

	Konst=InputBox("Zadajte očakávanú hodnotu rozptylu:","Zadanie rozptylu")
	C = CDbl(Konst)

	'Vyber alternativy
	alt = DisplayListBox(SelTypeOfAltHyp,TypeOfAltHyp,1)

	'do stl priradime index premennej,ktoru sme si vybrali
	stl = VarList(1)

	mean = 0
	For i = 1 To numcas
		mean = mean + Matrix (i,stl)
	Next i
	mean = mean / numcas

	pom1 = 0
	For i = 1 To numcas
		pom1 = pom1 + (Matrix (i,stl) - mean)^2
	Next i
	stdev = Sqrt(1/(numcas-1) * pom1)

	K = (numcas - 1) * stdev^2 / C

	If alt = 0 Then End
	If alt = 1 Then
		ChiAlphaPol = VChi2(alpha/2,numcas-1)
		Chi_1 = ChiAlphaPol
		Chi1MinusAlphaPol = VChi2(1-alpha/2,numcas-1)
		Chi_2 = Chi1MinusAlphaPol

		pmen = IChi2(K,numcas-1)			'vypocet p-hodnoty
		pvac = 1-IChi2(K,numcas-1)
		If (pmen <= pvac) Then
			pv = 2 * pmen
		Else
			pv = 2 * pvac
		End If

		ReDim Preserve A(6) As Double
		A(1) = mean
		A(2) = stdev
		A(3) = K
		A(4) = Chi_1
		A(5) = Chi_2
		A(6) = pv
		'Vysledna tabulka
		Set Summary = Spreadsheets.New
		Summary.SetSize(1,6)
		Summary.Header=SprHead
		Summary.CaseName(1) = SprC1
		Summary.AutoFitCase
		Summary.VariableName(1) = SprV1
		Summary.VariableName(2) = SprV2
		Summary.VariableName(3) = SprV3
		Summary.VariableName(4) = SprV4
		Summary.VariableName(5) = SprV5
		Summary.VariableName(6) = SprV8

		Summary.Variable(1).ColumnWidth=0.6
		Summary.Value(1,1) = A(1)
		Summary.Variable(2).ColumnWidth=0.7
		Summary.Value(1,2) = A(2)
		Summary.Variable(3).ColumnWidth=0.6
		Summary.Value(1,3) = A(3)
		Summary.Variable(4).ColumnWidth=1.2
		Summary.Value(1,4) = A(4)
		Summary.Variable(5).ColumnWidth=1.3
		Summary.Value(1,5) = A(5)
		Summary.Variable(6).ColumnWidth=1
		Summary.Value(1,6) = A(6)

		'Ak je p-hodnota mensia alebo rovna ako zadana hladina vyznamnosti, zamietame nulovu hypotezu, vsetko sa zobrazi na cerveno.
		If (pv <= alpha) Then
			For i = 1 To 6
			Summary.Cells(1,i).Font.Color = RGB(255,0,0)
			Next i
		End If
		Summary.Visible=True
	End If

	If alt = 2 Then
		Chialpha = VChi2(alpha,numcas-1)
		pv = IChi2(K,numcas-1)		'p-hodnota
		ReDim Preserve A(5) As Double
		A(1) = mean
		A(2) = stdev
		A(3) = K
		A(4) = Chialpha
		A(5) = pv
		'Vysledna tabulka
		Set Summary = Spreadsheets.New
		Summary.SetSize(1,5)
		Summary.Header=SprHead
		Summary.CaseName(1) = SprC1
		Summary.AutoFitCase
		Summary.VariableName(1) = SprV1
		Summary.VariableName(2) = SprV2
		Summary.VariableName(3) = SprV3
		Summary.VariableName(4) = SprV6
		Summary.VariableName(5) = SprV8

		Summary.Variable(1).ColumnWidth=0.6
		Summary.Value(1,1) = A(1)
		Summary.Variable(2).ColumnWidth=0.7
		Summary.Value(1,2) = A(2)
		Summary.Variable(3).ColumnWidth=0.6
		Summary.Value(1,3) = A(3)
		Summary.Variable(4).ColumnWidth=1.2
		Summary.Value(1,4) = A(4)
		Summary.Variable(5).ColumnWidth=1
		Summary.Value(1,5) = A(5)

		'Ak je p-hodnota mensia alebo rovna ako alpha, vsetko sa zobrazi na cerveno (zamietame H0).
		If (pv <= alpha) Then
			For i = 1 To 5
			Summary.Cells(1,i).Font.Color = RGB(255,0,0)
			Next i
		End If
		Summary.Visible=True
	End If

	If alt = 3 Then
		Chi1Minusalpha = VChi2(1-alpha,numcas-1)
		pv = 1 - IChi2(K,numcas-1)		'p-hodnota
		ReDim Preserve A(5) As Double
		A(1) = mean
		A(2) = stdev
		A(3) = K
		A(4) = Chi1Minusalpha
		A(5) = pv
		'Vysledna tabulka
		Set Summary = Spreadsheets.New
		Summary.SetSize(1,5)
		Summary.Header=SprHead
		Summary.CaseName(1) = SprC1
		Summary.AutoFitCase
		Summary.VariableName(1) = SprV1
		Summary.VariableName(2) = SprV2
		Summary.VariableName(3) = SprV3
		Summary.VariableName(4) = SprV7
		Summary.VariableName(5) = SprV8

		Summary.Variable(1).ColumnWidth=0.6
		Summary.Value(1,1) = A(1)
		Summary.Variable(2).ColumnWidth=0.7
		Summary.Value(1,2) = A(2)
		Summary.Variable(3).ColumnWidth=0.6
		Summary.Value(1,3) = A(3)
		Summary.Variable(4).ColumnWidth=1.2
		Summary.Value(1,4) = A(4)
		Summary.Variable(5).ColumnWidth=1
		Summary.Value(1,5) = A(5)

		'Ak je p-hodnota mensia alebo ako alpha, vsetko sa zobrazi na cerveno (zamietame H0).
		If (pv <= alpha) Then
			For i = 1 To 5
			Summary.Cells(1,i).Font.Color = RGB(255,0,0)
			Next i
		End If
		Summary.Visible=True
	End If

End Sub

'STATISTICA MACRO FILE VERSION 4
'BEGINPROPERTIES
'NAME=test_o_rozptylu
'DESCRIPTION=
'LANGUAGE=0
'SCRIPTING=0
'ENDPROPERTIES