Syntax Values Exercises

Note: For this task, ensure that all your solutions work on ethercalc.net spreadsheet. They usesocialCalc.
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