Create a worksheet that has the following matrix, which shows temperatures measu

Question

Create a worksheet that has the following matrix, which shows temperatures measured at 4 locations on 4 different days Develop a well-structured VBA code that stores the following matrix in one array variable mat(a, b) and then performs the following: Finds the maximum and minimum values in the table (to code a MinMax function, look Page 318); Calculates the average and standard deviation of each column [every day], the equations for average and standard deviation are below; Locates the results in your worksheet below each column; X bar = Average = sigma X_i/n sigma = Standard Deviation = squareroot sigma^n_i = 1 (X_i - X bar)^2/n - 1

Explanation / Answer

2)

Sub FindMinMax()
Dim MinValue, MaxValue
Set rangeVal = Sheet1.Range("B3:E6")
MinValue = Application.WorksheetFunction.Min(rangeVal)
MaxValue = Application.WorksheetFunction.Max(rangeVal)

MsgBox "Minimum value = " & MinValue & ", " & "Maximum value = " & MaxValue, vbInformation, "calculate"
End Sub

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sub CalculateAvgStdDev()

Dim tableRow As Long, table_lastrow As Long
Dim avg_val As Double, stdDev_val As Double
  
   avg_val = Application.WorksheetFunction.avg_val(Sheet1.Range("B3:B7"))
   stdDev_val = Application.WorksheetFunction.StDevP(Sheet1.Range("B3:B7"))   
   .Cells(tableRow, "B8").Value = avg_val
   .Cells(tableRow, "B9").Value = stdDev_val
  
   avg_val = Application.WorksheetFunction.avg_val(Sheet1.Range("C3:C7"))
   stdDev_val = Application.WorksheetFunction.StDevP(Sheet1.Range("C3:C7"))   
   .Cells(tableRow, "C8").Value = avg_val
   .Cells(tableRow, "C9").Value = stdDev_val
  
   avg_val = Application.WorksheetFunction.avg_val(Sheet1.Range("D3:D7"))
   stdDev_val = Application.WorksheetFunction.StDevP(Sheet1.Range("D3:D7"))   
   .Cells(tableRow, "D8").Value = avg_val
   .Cells(tableRow, "D9").Value = stdDev_val
  
   avg_val = Application.WorksheetFunction.avg_val(Sheet1.Range("E3:E7"))
   stdDev_val = Application.WorksheetFunction.StDevP(Sheet1.Range("E3:E7"))   
   .Cells(tableRow, "E8").Value = avg_val
   .Cells(tableRow, "E9").Value = stdDev_val
  
End Sub

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.