2 attachmentsSlide 1 of 2

  • attachment_1attachment_1
  • attachment_2attachment_2

UNFORMATTED ATTACHMENT PREVIEW

Note: For this task, ensure that all your solutions work on ethercalc.net spreadsheet. They use socialCalc. Task 1: In excel, anytime A1 changes to a different value, B1 counts. If the value in A1 is the same value as new input, it doesn’t count. What syntax would you use in excel? Task 1B: In excel, anytime A1 changes to a different value, B1 counts. Even if the value in A1 is the same value as new input, it counts. What syntax would you use in excel? Task 2: Use the following example to provide a syntax If 3.7 is entered into A2, B2 records the new number as first low count If 3.66 is entered into A2, B2 records new number as new low count If 3.90 is entered into A2, B2 records DOES NOT record number because it is not a new low count. If 3.10 is entered into A2, B2 records new number as new low count If 3.55 is entered into A2, B2 records DOES NOT record number because it is not a new low count. What syntax would you use in excel? Task 2B: Use the following example to provide a syntax If 3.7 is entered into A2, B2 records the new number as first high count If 3.66 is entered into A2, B2 DOES NOT record number because it is not a new high count If 3.90 is entered into A2, B2 records new number as new high count If 3.10 is entered into A2, B2 DOES NOT record number because it is not a new high count What syntax would you use in excel? Task 3A: Provide a syntax if same value is entered twice, to count on C2 For example, in cell A2, if 3.00 is entered first time, C2 will shows 0, B2 shows 0. If second time 3.20 is entered in A2, C2 & B2 won’t change If third time 3.20 is entered again in A2, C2 will count 1. And the matching value will show in B2. All these data will remain, and count will increase if matching numbers are repeated, unless there is a new matching value in which B2 shows the new value and C2 restart count again. Task 3B: Provide a syntax similar to the above scenario but this time, compare. If value in A2 is the same as B2, count in D2 and show matching value in C2. If it doesn’t match, don’t count in D2 or show value in C2. If it has already counted, don’t delete data in C2 or D2 unless there is a new matching value. Task 4A: Provide a syntax for a dynamic cell. If A2 cell value changes constantly from/to 2.9, 15, 2.7, 2.6, 2.8, 2.3, 2.5, 20and so on till infinite. In a downtrend scene, please provide a syntax that shows the lowest value in B2, and any next lowest value in C2 from various changing input of A2. Task 4B: Provide a syntax for a dynamic cell. If A2 cell value changes constantly from/to 2.9, 15, 2.7, 2.6, 2.8, 2.3, 2.5, 20 and so on till infinite. In an uptrend scene, please provide a syntax that shows the highest value in B2, and any next highest value in C2 from various changing input of A2. Task 5: Provide a syntax to keep old value of a cell. If first time input in A2 is 5. If second input is 20, previous number 5 will show in B2 cell. This continues for every changing value. Task 6: Provide syntax/methods for sheet cells value (NOT the FUNCTION) to reset based on time. E.g., A syntax to reset B2, C2 value based on the following time. To reset after 1 min To reset after 5 min To reset after 1 hour To reset after 5 hours Task 1: Using your previous code below, include a syntax that automatically reset B1 and B4 cell after every 2 minutes. The reset will not affect A1 input and if new input is entered after the reset, B1 starts counting again. Dim count As Integer Dim previousData As String Private Sub Worksheet_Change(ByVal Target As Range) Dim xRg As Range, xCell As Range On Error Resume Next If Target = Range(“A1”) Then Application.EnableEvents = False If count = 0 Then count = count + 1 Range(“B1”).Value = count previousData = Range(“A1”).Value ElseIf previousData Range(“A1”).Value Then count = count + 1 Range(“B1”).Value = count previousData = Range(“A1”).Value End If Application.EnableEvents = True End If End Sub Task 1B: A user is tasked with copying numbers from pdf to excel cell. Due to unknown error, the numbers copied are usually joined with the next text in front it. Example 1: – On pdf page, there is spacing between 200.09215 and 0.10 but when copied into excel it joined and shows 200.092150.10 in excel. The current value user needed is 200.09215. Example 2: On pdf page, there is spacing between 200.09215 and (109%) but when copied into excel it joined and shows 200.09215(109%) in excel. The current value user needed is 200.09215. Example 3: On pdf page, there is spacing between 200.09 and -102 but when copied into excel it joined and shows 200.09-102 in excel. The current value user needed is 200.09. Example 4: On pdf page, there is spacing between 200.09215 and 3.28 but when copied into excel it joined and shows 200.092153.28 in excel. The current value user needed is 200.09215. Based on the issue above, write a syntax that only shows the correct value. Task 2: The code below works but you are tasked to do the following 1. If the current new value has no match, display 0 in C2. If it matches, keeping counting based on previous count number recorded for that input. 2. 3. Display the Highest value with the most count in A7 and what the count number was in B7. On C7, if data is found, show Good. If none, show noData. Display the Lowest value with the most count in A10 and what the count number was in B10. On C10, if data is found, show Good. If none, show noData. Task 2B: Using the same approach as above, allow user to enter input count values either in A7 or A10. Then based on which cell user entered the count value, – Display result in B7 with the highest value with that count number. On C7, if data is found, show Good. If none, show noData. Display result in B7 with the lowest value with that count number. On C10, if data is found, show Good. If none, show noData. Dim c() As Double Dim d() As Double Dim iCtr As Integer Dim iCtr2 As Integer Dim pos Private Function PosInArray(vFind As Double, arr1 As Variant) As Variant Dim i As Long For i = LBound(arr1) To UBound(arr1) If arr1(i) = vFind Then PosInArray = i Exit Function End If Next i PosInArray = -1 End Function Private Sub Worksheet_Change(ByVal Target As Range) Dim xRg As Range, xCell As Range If Target = Range(“A2”) Then If (Not Not c) = 0 Then ReDim c(0) ReDim d(0) iCtr = 0 End If Application.EnableEvents = False If iCtr 0 Then pos = PosInArray(CStr(Range(“A2”).Value), c) End If If iCtr = 0 Then c(iCtr) = CStr(Range(“A2”).Value) d(iCtr) = 1 Range(“B2”).Value = 0 Range(“C2”).Value = 0 iCtr = iCtr + 1 ElseIf pos -1 Then Range(“B2”).Value = Range(“A2”).Value Range(“C2”).Value = d(pos) + 1 iCtr2 = d(pos) + 1 d(pos) = iCtr2 Else ReDim Preserve c(iCtr) ReDim Preserve d(iCtr) c(iCtr) = CStr(Range(“A2”).Value) d(iCtr) = 1 iCtr = iCtr + 1 End If Application.EnableEvents = True End If End Sub

Do you similar assignment and would want someone to complete it for you? Click on the ORDER NOW option to get instant services at essayloop.com

Do you have a similar assignment and would want someone to complete it for you? Click on the ORDER NOW option to get instant services at essayloop.com. We assure you of a well written and plagiarism free papers delivered within your specified deadline.