Question

Statistics Macro Assignment

Write a macro to find the number of observations, max, min, sum, average and standard deviation for a column of numbers with any number of observations starting in cell A1 and proceeding downward. The number set will be of any length and include negative, zero, and positive integers. Display the results as shown below. Use the numbers below as an example.

You may use the key board code “Selection.End(xlDown).Select” if you wish. Otherwise, use only VBA code to accomplish these tasks. No Excel functions may to be used in the spreadsheet. Name the Excel file “Statistics”, name macro “Statistics” and create a “RUN” button on the spreadsheet. Remember to dimension all variables. The macro will be graded using a different data set.

Count 22.00 Max-9.00 Min -6.00 1 Sum-64.00 Avg. -2.91 4.26 Descriptive Statistics Std. Dev. 2 10 12 13 15 16 17 18 19 20 1

As simple as possible please

Answer 1

Macro Code:

Sub Statistics()

MsgBox ("Success Statistics Report")
Dim r As Range, sum As Integer, average As Double, count As Integer, Max As Integer, Min As Integer

Set r = Sheets("Sheet1").Range("A:A")
sum = Application.WorksheetFunction.sum(r)
average = Application.WorksheetFunction.average(r)
count = Application.WorksheetFunction.count(r)
Max = Application.WorksheetFunction.Max(r)
Min = Application.WorksheetFunction.Min(r)
sd = Application.WorksheetFunction.StDev(r)
  

Worksheets(1).Range("G3").Value = count
Worksheets(1).Range("G6").Value = sum
Worksheets(1).Range("G7").Value = average
Worksheets(1).Range("G4").Value = Max
Worksheets(1).Range("G5").Value = Min
Worksheets(1).Range("G8").Value = sd

End Sub

Output picture:

Statistics -Excel FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER AutoSum A Cut Calibri 11A A fg Copy . ·ク·A. fE栏自Merge & Center. $. % , Conditional Format as Cell Formatting Table Styles Sort & Find & Filter Select Insert Delete Format Format Painter Blu, 田 Clear Clipboard Font Alignment Number Cells Editing Count Max Min 110 7.333333 Standard Deviation13.59447 Descriptive Statistics 14 0

Microsoft Visual Basic for Applications- Statistics.xdsx - [Modulel (Code)] File Edit View Insert Format Debug Bun Tools Add-ins window Help Ln 20, Col 54 (General) vStatistics Sub Statistics ) MsgBox ("Success Statistics Report") Dim r As Range, sum As Integer, average As Double, count As Integer Set r-Sheets ("Sheeti" Range("A:A") mApplication.WorksheetFunction.sum () average Application.WorksheetFunction.average (r) count-Application.WorksheetFunction.count (r) MaxApplication.WorksheetFunction.Max (r) MinApplication.WorksheetFunction.Min (r) sdApplication.WorksheetFunction.StDev(r) Worksheets (i). Range ("G3").Valuecount Worksheets (1) Range ("G6") .Valuesum Worksheets (1) Range ("G7").Value average Worksheets (1) . Range ("G4" ) . Value Worksheets (1). Range ("G5").value Max -M1 Worksheets (1) Range ("G8") Value-sd End Sub

Please give a thumbs up......