Question

In: Computer Science

In VBA, write a code that does as follows: The first worksheet ("Ex. 1") has a...

In VBA, write a code that does as follows: The first worksheet ("Ex. 1") has a list of 50 numbers. Write a program that will read them into an array, then will calculate and output the following: - How many of the numbers are even (output in E2) - How many of the numbers are greater than 300 (output in E3) - The average of the numbers (output in E4) - In column B, output next to each number, the difference between that number and the average (can be positive or negative)

Solutions

Expert Solution

In case of any query, do comment. Please rate answer. Thanks

Please use below code in your module:

Sub Numbers()

'

' Numbers Macro

'

'

'to select the worksheet

Application.Workbooks("Numbers.xlsm").Worksheets("Ex.1").Select

Dim numberArray As Variant

'read cell range from 2 to 51 in an array

numberArray = Application.Transpose(Range("A2:A51"))

Dim average As Integer

Dim sum As Integer

Dim evenCount As Integer

Dim numberGreaterThan300 As Integer

    'iterate over array

    For i = 1 To 50

        sum = sum + numberArray(i) 'add to sum

        If numberArray(i) > 300 Then

            'if number is greater than 300 then increase numberGreaterThan300 count

            numberGreaterThan300 = numberGreaterThan300 + 1

        End If

       

        If numberArray(i) Mod 2 = 0 Then

            'if number is even, increase even count

            evenCount = evenCount + 1

        End If

    Next

   

average = sum / 50 'find the average

Cells(2, 5).Value = evenCount 'put even count in E2

Cells(3, 5).Value = numberGreaterThan300 'put numbers count greater than 300 in E3

Cells(4, 5).Value = average 'put average in E4

For j = 2 To 51

    ' substract average from A column and put it in B

    Cells(j, 2) = Cells(j, 1) - average

Next

End Sub

====================screen shot of the code=========================

Output:


Related Solutions

write a VBA code to convert an arbitrary positive binary fractional number (0< number<1) to equivalent...
write a VBA code to convert an arbitrary positive binary fractional number (0< number<1) to equivalent decimal number. the code should acquire the binary fraction number in the format"0.xxxxxx"from input box, then return the equivalent decimal number in a message box. in the code, you may need to use VBA function "mid(_,_,_)" to pick up a specific symbols or characters from a string. you can use below conversion as benchmark to verify and debug your code: (0.1011)2 = (0.6875)10   
Please write a VBA code to solve the solution for below non-linear equation by NewtonRaphson method...
Please write a VBA code to solve the solution for below non-linear equation by NewtonRaphson method with convergence criteria e = 10 ^ -6, where index is your class index number. If your index is odd number, please use initial trial x = -5, if your index number is even number, please use initial trial x = 5. Return your solution in a message box. ? = ? 2 ????? + 1 − ???(?) = 0 Index = 12
develop a vba computer code to solve the initial value first ODE:dy/dt =(4y)/t with the initial...
develop a vba computer code to solve the initial value first ODE:dy/dt =(4y)/t with the initial condition of y (1)=2 with a time step of 0.05 for the time interval 1 <=t<= 2 for the euler method,2nd order runge-kutta method and the 4th order runge-kutta method
The third worksheet ("Ex. 3") contains 2 4x4 matrices, plus space for a third. Write a...
The third worksheet ("Ex. 3") contains 2 4x4 matrices, plus space for a third. Write a program that will read the first 2 matrices into array "B" and "C" . Create a 3rd array, "A" that is the result of matrix B + matrix C (i.e |A| = |B| + |C|). Output that array in the indicated cells (upper left = cell K1) To do this, use the rule for array addition for each matrix element: aij = bij +...
Using the first code of this lab (Figure 1), write a code that displays the status...
Using the first code of this lab (Figure 1), write a code that displays the status of a push button on the LCD, that is, when you press it you should see “Pushed” on the LCD and when you release it, you should see “Released” #include <LiquidCrystal.h> // initialize the library with the numbers of the interface pins LiquidCrystal lcd(12, 11, 5, 4, 3, 2); void setup() { // set up the LCD's number of columns and rows: lcd.begin(16, 2);...
Write code for a short method that does the following: accepts two strings as parameters, first...
Write code for a short method that does the following: accepts two strings as parameters, first name, and last name; Outputs the following message, concatenated together in one line of output:
Scissors Hair Stylists has begun the preparation of its worksheet as​ follows: Complete Scissors worksheet through...
Scissors Hair Stylists has begun the preparation of its worksheet as​ follows: Complete Scissors worksheet through the adjusted trial balance section. In the adjustments​ section, mark each adjustment by letter. a. Office supplies on​ hand, $ 300 b. Depreciation, $ 900 c. Accrued interest​ expense, $ 400. Scissors Hair Stylists Worksheet December 31, 2018 Unadjusted Adjusted Trial Balance Adjustments Trial Balance Account Names Debit Credit Debit Credit Debit Credit Cash $700 Office Supplies 500 Equipment 20,200 Accumulated Depreciation—Equipment $900 Accounts...
write a java code to represent a sales class as follows: 1- The Sales class contains...
write a java code to represent a sales class as follows: 1- The Sales class contains the names of sellers (strings) and the sales/seller/day (matrix of integers). Assume the number of sellers is set dynamically by the constructor and that the sellers work 6 days/week. Example: names/days 0 1 2 3 4 5 Ali 30 5 89 71 90 9 Ahmad 15 81 51 69 78 25 Omar 85 96 7 87 41 54 The class should contain the following...
The worksheet for Major Advising to complete this exercise is as follows. 1. Prepare the closing...
The worksheet for Major Advising to complete this exercise is as follows. 1. Prepare the closing entries. If an amount box does not require an entry, leave it blank. Do not enter the posting references until you complete part 2. 2. Post the closing entries to the T accounts. If there is more than one closing entry for an account, enter in the order given in the journal. Then complete the posting in part 1. Major Advising Work Sheet (Partial)...
write the code based on this pseudocode //Create Class any name (ex: aQ) //Declaring private integer...
write the code based on this pseudocode //Create Class any name (ex: aQ) //Declaring private integer array empty one ex: name arrQ[] //Declare private integers front, rear, size and len (example, but you call any other names) //Write constructors Called public and same name of the class (parameters integer n) Inside Declare where: size is equal to n len is equal to zero arrQ is equal to new integer[size] front is equal minus 1 rear is equal minus 1 //Declare...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT