Please help me solve this step by step using excelSolutiona

Please help me solve this step by step using excel.

Solution

a)

Here we are assuming that worksheet may contains flight number more than 6 also.

Required VBA Code for 15 rows for the provided worksheet is shown below

Public Sub no_of_passengers_flight()

Dim noOfPassenger As Double

Set myRange = Worksheets(\"Sheet1\").Range(\"C2:C15\")

noOfPassenger = Application.WorksheetFunction.CountIfs(myRange, \">=1\", myRange, \"<=6\")

MsgBox noOfPassenger

End Sub

b)

Public Sub avg_amt()

Range(\"J25\").Value = Application.WorksheetFunction.Average(Range(\"J2:J15\"))

End Sub

c)

Public Sub max_amt()

Range(\"J27\").Value = Application.WorksheetFunction.Max(Range(\"J2:J15\"))

End Sub

Public Sub min_amt()

Range(\"J28\").Value = Application.WorksheetFunction.Min(Range(\"J2:J15\"))

End Sub

d)

Here again we are assuming classes can be more than what has been specified in given worksheet.

Public Sub sum_amt()

Dim tot_bus_amt, tot_eco_amt, tot_club_amt As Double

Set myClassRange = Worksheets(\"Sheet1\").Range(\"D2:D15\")

Set mySumRange = Worksheets(\"Sheet1\").Range(\"J2:J15\")

tot_bus_amt = Application.WorksheetFunction.SumIfs(mySumRange, myClassRange, \"Economy\")

tot_eco_amt = Application.WorksheetFunction.SumIfs(mySumRange, myClassRange, \"Business\")

tot_club_amt = Application.WorksheetFunction.SumIfs(mySumRange, myClassRange, \"Club\")

Range(\"J30\").Value = tot_bus_amt + tot_eco_amt + tot_club_amt

End Sub