Afia Kona

Dear Tanzir,
Thank you for your comment.
Arcsine transform is done for real numbers ranging from 0 to 1.
In the formula, DEGREES(ASIN(SQRT(X/100))), X indicates the percent value to be transformed. Therefore, when we put 50% in the formula it actually becomes 0.5. As a result, we can Arcsine transform the data.
However, if we put 50 instead of 50% then Arcsine transform is not possible.

Regards
Afia Aziz Kona

Dear Sam,
Thank you for your comment. I understand that you are looking for a more dynamic Attendance Sheet, if so, you can use the following VBA code as an update:

Private Sub Worksheet_Change(ByVal Target As Range)

'Developed by ExcelDemy

If Target.Address = "$C$4" Or Target.Address = "$C$5" Then

    totalDays = Array(31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
    
    If Range("C5").Value Mod 4 = 0 Then
        totalDays(1) = 29
    End If

    Dim specificDate As Date
    Dim dayOfWeek As Integer
    Dim dayName As String
    
    
    monthNumber = Month(DateValue(Range("C4").Value & " 1 2000"))
    yearNumber = Range("C5").Value
    
    Range(Range("D7").Cells(1, 1), Range("D7").Cells(6, 31 + 2)).ClearContents
    Range(Range("D7").Cells(1, 29), Range("D7").Cells(6, 31 + 2)).ClearFormats
    
    For i = 1 To totalDays(monthNumber - 1)
    
    ' Specify the date
        specificDate = DateSerial(yearNumber, monthNumber, i)

    ' Get the day of the week as a number (1 for Sunday, 2 for Monday, etc.)
        dayOfWeek = Weekday(specificDate, vbSunday)

    ' Get the name of the day of the week
        dayName = WeekdayName(dayOfWeek, False, vbSunday)

    ' Display the name of the day
        Range("D7").Cells(1, i) = i
        Range("D8").Cells(1, i) = Left(dayName, 3)
         
    Next i
    
    Range("D7:D12").Copy
    For i = 29 To totalDays(monthNumber - 1)
        Range("D7:D12").Cells(1, i).PasteSpecial Paste:=xlPasteFormats
        Range("D7:D12").Cells(1, i).ColumnWidth = Range("D7").Cells(1, 1).ColumnWidth
    Next i
    
    Application.CutCopyMode = False
    
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 1) = "Present"
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 1).Interior.Color = vbGreen
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 1).ColumnWidth = 8
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 1).Font.Bold = True
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 1).HorizontalAlignment = xlHAlignCenter
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 1).VerticalAlignment = xlVAlignCenter
    
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 2) = "Absent"
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 2).Interior.Color = vbGreen
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 2).ColumnWidth = 8
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 2).Font.Bold = True
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 2).HorizontalAlignment = xlHAlignCenter
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 2).VerticalAlignment = xlVAlignCenter
    
    For j = 1 To 2
        For k = 1 To 5
            With Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 1).Cells(k, j)
                .Borders(xlEdgeTop).LineStyle = xlContinuous
                .Borders(xlEdgeTop).Color = RGB(0, 0, 0)
                .Borders(xlEdgeTop).Weight = xlThin
    
                .Borders(xlEdgeBottom).LineStyle = xlContinuous
                .Borders(xlEdgeBottom).Color = RGB(0, 0, 0)
                .Borders(xlEdgeBottom).Weight = xlThin
    
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Color = RGB(0, 0, 0)
                .Borders(xlEdgeLeft).Weight = xlThin
    
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Color = RGB(0, 0, 0)
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        Next k
    Next j
End If
End Sub

Download this Excel file for a better understanding.
I hope that your problem will be solved now. If you have any further issue, please let us know in the comment section.

Best
Afia Aziz Kona
Excel and VBA Content Developer
Exceldemy

Dear CONCERNEDEXCELUSER,
Thank you for your comment.
Please have a look at the graph, the error bar for Study 6 is at the topmost position. And point 1.88 is right next to point 1.84.
Thank you.

Regard,
Afia Aziz Kona
Content Developer
Exceldemy

Dear Damien,
Thank you for your comment. To get “zero” or “Nil” for the value 0, you have to simply add an IF statement with the code.
you can use the following code:

 Function number_converting_into_words(MyNumber)

Dim x_string As String
Dim whole_num As Integer
Dim x_string_pnt
Dim x_string_Num
Dim x_pnt As String
Dim x_numb As String
Dim x_P() As Variant
Dim x_DP
Dim x_cnt As Integer
Dim x_output, x_T As String
Dim x_my_len As Integer

On Error Resume Next

x_P = Array("", "Thousand ", "Million ", "Billion ", "Trillion ", " ", " ", " ", " ")
x_numb = Trim(Str(MyNumber))
x_DP = InStr(x_numb, ".")
x_pnt = ""
x_string_Num = ""

If MyNumber = 0 Then
    number_converting_into_words = "zero"
    
Else

    If x_DP > 0 Then
        x_pnt = " point "
        x_string = Mid(x_numb, x_DP + 1)
        x_string_pnt = Left(x_string, Len(x_numb) - x_DP)
    
        For whole_num = 1 To Len(x_string_pnt)
            x_string = Mid(x_string_pnt, whole_num, 1)
            x_pnt = x_pnt & get_digit(x_string) & " "
        Next whole_num
        
        x_numb = Trim(Left(x_numb, x_DP - 1))
    End If
    
    x_cnt = 0
    x_output = ""
    x_T = ""
    x_my_len = 0
    x_my_len = Int(Len(Str(x_numb)) / 3)
    
    If (Len(Str(x_numb)) Mod 3) = 0 Then x_my_len = x_my_len - 1
    
        Do While x_numb  ""
            If x_my_len = x_cnt Then
                x_T = get_hundred_digit(Right(x_numb, 3), False)
            Else
                If x_cnt = 0 Then
                    x_T = get_hundred_digit(Right(x_numb, 3), True)
                Else
                    x_T = get_hundred_digit(Right(x_numb, 3), False)
                End If
            End If
            If x_T  "" Then
                x_output = x_T & x_P(x_cnt) & x_output
            End If
            If Len(x_numb) > 3 Then
                x_numb = Left(x_numb, Len(x_numb) - 3)
            Else
                x_numb = ""
            End If
            x_cnt = x_cnt + 1
        Loop
        x_output = x_output & x_pnt
        number_converting_into_words = x_output
    End If

End Function
Function get_hundred_digit(xHDgt, y_b As Boolean)
Dim x_R_str As String
Dim x_string_Num As String
Dim x_string As String
Dim y_I As Integer
Dim y_bb As Boolean
x_string_Num = xHDgt
x_R_str = ""
On Error Resume Next
y_bb = True
If Val(x_string_Num) = 0 Then Exit Function
x_string_Num = Right("000" & x_string_Num, 3)
x_string = Mid(x_string_Num, 1, 1)
If x_string  "0" Then
x_R_str = get_digit(Mid(x_string_Num, 1, 1)) & "Hundred "
Else
If y_b Then
x_R_str = "and "
y_bb = False
Else
x_R_str = " "
y_bb = False
End If
End If
If Mid(x_string_Num, 2, 2)  "00" Then
x_R_str = x_R_str & get_ten_digit(Mid(x_string_Num, 2, 2), y_bb)
End If
get_hundred_digit = x_R_str
End Function
Function get_ten_digit(x_TDgt, y_b As Boolean)
Dim x_string As String
Dim y_I As Integer
Dim x_array_1() As Variant
Dim x_array_2() As Variant
Dim x_T As Boolean
x_array_1 = Array("Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen ")
x_array_2 = Array("", "", "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", "Seventy ", "Eighty ", "Ninety ")
x_string = ""
x_T = True
On Error Resume Next
If Val(Left(x_TDgt, 1)) = 1 Then
y_I = Val(Right(x_TDgt, 1))
If y_b Then x_string = "and "
x_string = x_string & x_array_1(y_I)
Else
y_I = Val(Left(x_TDgt, 1))
If Val(Left(x_TDgt, 1)) > 1 Then
If y_b Then x_string = "and "
x_string = x_string & x_array_2(Val(Left(x_TDgt, 1)))
x_T = False
End If
If x_string = "" Then
If y_b Then
x_string = "and "
End If
End If
If Right(x_TDgt, 1)  "0" Then
x_string = x_string & get_digit(Right(x_TDgt, 1))
End If
End If
get_ten_digit = x_string
End Function
Function get_digit(xDgt)
Dim x_string As String
Dim x_array_1() As Variant
x_array_1 = Array("Zero ", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine ")
x_string = ""
On Error Resume Next
x_string = x_array_1(Val(xDgt))
get_digit = x_string
End Function 

Best,
Afia Aziz Kona

Dear AAMIR
Thank you for your comment.
If you want to make a recorder for only half-day casual leave, you can use the same template in this article. In this case, each “1” will be a half-day leave. And the total will imply a total half day’s leave. Change the header for your needs.

Also, for better reach, you can review the following article to record a half-day casual leave.
https://www.exceldemy.com/calculate-half-day-leave-in-excel/

Best,
Afia Aziz Kona

Dear Deepak,
Thank you for your comment.
To show 100 in value 25=100/4, you need to apply the following Custom Cell Format.

00/4

Best,
Afia Aziz Kona

Dear Yos,
Walaikum’assalam. Thank you so much for your comment. I will try my best to give you a proper solution.
Firstly, I will show you how you can import a .pdf file to a .txt file, and after that, I will show you the VBA code to open the text file in Excel.

Let me show you how you can import a .pdf file to a .txt file.

● First of all, upload your PDF file to your Google Drive.
● Then, from Google Drive >> right-click on the uploaded PDF file.
● Then, click on Open With from the Context Menu >> select Google Docs.
This will open the PDF as a Google Doc file.

● After that, go to the File tab of Google Docs >> click on Download.
● This will bring out several Download options >> select Plain Text (.txt)
This will make the Google Docs as a Text file.
● Next, open your Text file and see the outcome.

Next, I will show you how you can open the Text file in Excel using VBA.
In the beginning, carefully notice the location of the Text file, and also carefully note the name of the file.
Here, we have marked our text file name.
This is because we have to implement the file name and location in the VBA code properly.

Now, it is time to open a VBA Module.
● To open a VBA module, open your Excel file >> go to the Developer tab >> select Visual Basic.
You can also press the ALT+F11 keys.
This will open a VBA Editor window.
● Furthermore, from the Insert tab >> select Module.
● Moreover, in the Module, type the following code.

Sub ConvertToNewWorkbook()
    Dim iBook As Workbook
    Dim iTexts As Workbook
    Dim iSheet As Worksheet
    Set iBook = ThisWorkbook
    Set iSheet = iBook.Sheets(1)
    Set iTexts = Workbooks.Open("C:\ExcelDemy\Student List.txt")
    iBook.Sheets(1).Cells.Copy iSheet.Cells
    iBook.Close SaveChanges:=True
End Sub

Here, change the location and name of the text file according to your file.
● Afterward, Save the code >> Run the code.
Therefore, you will see the Excel file will have all the texas.
I hope this was helpful. Please let us know if you have any additional queries.

Regard,
Afia Aziz Kona

Dear ABBY SHULER
Thank you for your comment. Here, for your first problem, to get rid of the #NAME error, you can copy the following VBA code in a new Excel Workbook. The uploaded Excel file sometimes show problem while running on a different computer, therefore, when you will copy the code to a new workbook, the #NAME error will be solved.

Function AddressCompare(first_string As String, Second_string As String, _
Comparing_Letters As Integer) As Double

Dim int_character As Integer, Comparing_LettersMatch As Integer
Dim n_Gram_List1 As String, n_Gram_List2 As String, n_letter_Gram As Variant
Dim n_Gram_array1 As Variant

For int_character = 1 To Len(first_string) – (Comparing_Letters – 1)
If n_Gram_List1 <> “” Then n_Gram_List1 = n_Gram_List1 & “,”
n_Gram_List1 = n_Gram_List1 & Mid(first_string, int_character, Comparing_Letters)
Next int_character

For int_character = 1 To Len(Second_string) – (Comparing_Letters – 1)
If n_Gram_List2 <> “” Then n_Gram_List2 = n_Gram_List2 & “,”
n_Gram_List2 = n_Gram_List2 & Mid(Second_string, int_character, Comparing_Letters)
Next int_character

n_Gram_array1 = Split(n_Gram_List1, “,”)

For Each n_letter_Gram In n_Gram_array1
If InStr(1, n_Gram_List2, n_letter_Gram) Then
Comparing_LettersMatch = Comparing_LettersMatch + 1
End If
Next n_letter_Gram

AddressCompare = Comparing_LettersMatch / (UBound(n_Gram_array1) + 1)
End Function

After that, save the code and go back to your worksheet. I hope the #NAME error will be gone now.

For your second query, you can use 3 instead of 2. Using 3 will compare 3 letters at a time, and therefore, the result will decrease the match percentage between two addresses.
When you will use 2, it will compare 2 letters at a time, and therefore, the match percentage between two addresses will be higher.
Let me show you that elaborately.
When we use =AddressCompare(C5, F5, 2) in cell E3, the result becomes 1, which indicates the Exact Match.
However, the two addresses are not the same, therefore, using 2 does not give an accurate result.

On the other hand, when we use the formula =AddressCompare(C5, F5, 3) in cell E3, the result becomes 0.970588235, which does not indicate the Exact Match. Rather, it suggests that there is some dissimilarity between the two addresses.
Therefore, using 3 is wise in your case.

Here, another thing must be noted, for your address match, you have to set your own creation while using the IF function.
Let me elaborate on this.
Here, in cell F3, we type the following formula.
IF(AddressCompare(C5, F5, 3)>0.5, "Full Match", "No Match")
Here, in this formula, we give the criteria, that when AddressCompare(C5, F5, 2) is greater than 0.5, the IF function returns “Full Match“. However, when AddressCompare(C5, F5, 2) is not greater than 0.5, the IF function returns “No Match“.
Therefore, in cell F3 the result is “Full Match“.
However, there is some dissimilarity between the two addresses. Hence, the result in cell F3 is not accurate.

To get an accurate result, we will type the following formula in cell F3.

IF(AddressCompare(C5, F5, 3)>0.99, "Full Match", "No Match")
Here, in this formula, we give the criteria, that when AddressCompare(C5, F5, 2) is greater than 0.99, the IF function returns “Full Match“. However, when AddressCompare(C5, F5, 2) is not greater than 0.99, the IF function returns “No Match“.
Therefore, in cell F3 the result is “No Match“.
This is the correct result.

I really hope that you get your answer, and that you can solve your problems.
If you face any problem, you can always let us know.

Regards,
Afia Aziz Kona

Dear Jacob Smith
Thank you for your comment.
The first method works properly when you press the SHIFT key after selecting rows/columns.
Let me explain this elaborately.
Here, in the following dataset, we want to move rows 7 and 8.

To do so, first, we will select cells B7:D8.
After that, we will hover our mouse cursor to the edge of the selection and wait for it to change into a 4 directional cross.

At this point, press the SHIFT key.
Along with that, left-click on it with your mouse and drag your selection to the desired location.
A green line should appear to assist you to drop it at the desired location.
Here, we drag the selected rows to Row 3, and therefore, you can see a Green Line at Row 3.
In addition, you can see B4:D5 on the left side of the Green line which indicates the final location of the selected rows.
After that, we will release the mouse and SHIFT key.

As a result, you can see the movement of rows to a new location.

I hope that you can now use Method-1.
If you have any problems, you can always let us know in the comment section.

Regard
Afia Aziz Kona