Solution Test Exam 2021/05/27

Option Explicit

' Average age of people working as 'impiegato'.
' Important: the work field is not written always in the same way. Sometimes lower case, other times upper case...
Function esercizio_1() As Double
    Dim i As Integer, n As Integer
    esercizio_1 = 0
    n = 0
    For i = 2 To 21
        If LCase(Cells(i, 4)) = "impiegato" Then
            esercizio_1 = esercizio_1 + Cells(i, 3)
            n = n + 1
        End If
    Next i
    esercizio_1 = esercizio_1 / n
End Function

' Minimum age of people with name starts with "m" (or "M") and surname ends with "o" (or "O").
Function esercizio_2() As Integer
    Dim i As Integer
    esercizio_2 = -1
    For i = 2 To 21
        If Left(LCase(Cells(i, 1)), 1) = "m" And Left(LCase(Cells(i, 2)), 1) = "o" And (Cells(i, 3) < esercizio_2 Or esercizio_2 = -1) Then
            esercizio_2 = Cells(i, 3)
        End If
    Next i
End Function

' Standard deviation of people age that are students.
Function esercizio_3() As Double
    Dim i As Integer, n As Integer
    Dim avg As Double
    avg = 0
    n = 0
    For i = 2 To 21
        If LCase(Cells(i, 4)) = "studente" Then
            avg = avg + Cells(i, 3)
            n = n + 1
        End If
    Next i
    avg = avg / n
    esercizio_3 = 0
    For i = 2 To 21
        If LCase(Cells(i, 4)) = "studente" Then
            esercizio_3 = esercizio_3 + (Cells(i, 3) - avg) ^ 2
            n = n + 1
        End If
    Next i
    esercizio_3 = esercizio_3 / n
End Function

' Number of people with age gretear than the result of previous exercize.
Function esercizio_4() As Integer
    Dim i As Integer, ref As Double
    ref = esercizio_3()
    esercizio_4 = 0
    For i = 2 To 21
        If Cells(i, 3) < ref Then
            esercizio_4 = esercizio_4 + 1
        End If
    Next i
End Function

' Number of people with age between the average age of student and "libero professionista".
Function esercizio_5() As Integer
    Dim i As Integer, n_stud As Integer, n_lib_prof As Integer
    Dim avg_stud As Double, avg_lib_prof As Double
    avg_stud = 0
    avg_lib_prof = 0
    n_stud = 0
    n_lib_prof = 0
    For i = 2 To 21
        If LCase(Cells(i, 4)) = "studente" Then
            avg_stud = avg_stud + Cells(i, 3)
            n_stud = n_stud + 1
        ElseIf LCase(Cells(i, 4)) = "libero professionista" Then
            avg_lib_prof = avg_lib_prof + Cells(i, 3)
            n_lib_prof = n_lib_prof + 1
        End If
    Next i
    avg_stud = avg_stud / n_stud
    avg_lib_prof = avg_lib_prof / n_lib_prof
    esercizio_5 = 0
    For i = 2 To 21
        If (Cells(i, 3) >= avg_stud And Cells(i, 3) <= avg_lib_prof) Or (Cells(i, 3) <= avg_stud And Cells(i, 3) >= avg_lib_prof) Then
            esercizio_5 = esercizio_5 + 1
        End If
    Next i
End Function