Mursalin Ibne Salehin

About author

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning, and Python.

Designation

Team Leader at ExcelDemy in SOFTEKO.

Lives in

Dhaka, Bangladesh.

Education

B.sc in Electrical and Electronics Engineering (EEE), BUET.

Expertise

Content Writing, VBA, C, C++, Python, Matlab, HTML, AutoCAD, Microsoft Office.

Experience

  • Technical Content Writing
  • Team Management
  • Undergraduate Projects
    • Neural Machine Translation using GAN
    • Rewinding & Speed Measurement of Single Phase Induction Motor

Summary

  • Currently working as a Team Leader of ExcelDemy.
  • Started technical content writing of Excel & VBA in January 2022 later prompted as Team Leader for Content Management.

Latest Posts From Mursalin Ibne Salehin

0
How to Calculate BMI with Formula in Excel (3 Cases)

BMI is an important term to assess an individual’s body fat based on height and weight. It can be used to screen people who are underweight, overweight, or ...

0
Excel Charts: A Complete Overview

Charts in Excel are used to represent data graphically. There are many types of charts in Excel that you can use based on the data. In this Excel tutorial, ...

0
How to Copy Above Cell in Excel (3 Easy Ways)

"Copying above cell" in Excel is useful when you need to repeat or follow a specific order with your data, especially when your information is arranged in ...

1
How to Create a Questionnaire in Excel (2 Easy Ways)

In this article, we will learn to create a questionnaire in Excel. A questionnaire is a set of questions or specific items with options. It is used to collect ...

1
How to Convert Alphabet to Number in Excel (4 Easy Ways)

In this article, we will learn to convert the alphabet to number in Excel. In Excel, we can use different functions to change an alphabet to a number. Today, ...

1
How to Extract Data from XML File to Excel (2 Easy Ways)

In this article, we will learn to extract data from an XML file to Excel. The XML format is mainly used on the web to store data. Also, we can save it on our ...

0
Advanced Uses of CHOOSE Function in Excel (9 Applications)

In this article, we will learn the advanced uses of the CHOOSE function in Excel. It returns a value from a list using a specified position or index. The ...

1
Create Average Daily Balance Calculator in Excel (2 Easy Methods)

In this article, we will learn to create an average daily balance calculator in Excel. Credit card companies use the Average Daily Balance Method to charge ...

0
How to Swap Text in Excel (3 Easy Methods)

Swapping text in Excel refers to interchanging or exchanging the positions of words, numbers, or phrases within a cell. This action is commonly used to ...

0
Excel VLOOKUP with Multiple Criteria in Column and Row

In this article, we will learn to use the VLOOKUP function with criteria in the column and row in Excel. The VLOOKUP function looks for a value in the leftmost ...

0
Excel COUNTIFS with Multiple Criteria and OR Logic (3 Examples)

In this article, we will learn to use the COUNTIFS function with multiple criteria and OR logic in Excel. The COUNTIFS function counts the number of cells that ...

0
How to Create Button Without Macro in Excel (3 Easy Ways)

In this article, we will learn to create a button without a macro in Excel. Usually, users create a button in Excel with the help of a macro to perform a ...

0
How to Display Long Numbers in Excel (4 Easy Ways)

In this article, we will learn to display long numbers in Excel. Excel displays whole numbers to 11 digits. After 11 digits, it displays the numbers in ...

0
How to Plot Michaelis Menten Graph in Excel (With Easy Steps)

In this article, we will learn to plot Michaelis Menten graph in Excel. We use the Michaelis Menten equation to plot the graph. Generally, it is used to ...

0
Excel VBA to Select First Visible Cell in Filtered Range

In this article, we will learn to select the first visible cell in a filtered range with Excel VBA. We can select the first visible cell in a filtered range ...

Browsing All Comments By: Mursalin Ibne Salehin
  1. Hi JUN,

    Thanks for your comment. I assume you are having this problem because Excel considers 1 (‘dataset’!=”1″) as a number. You don’t need to add the double quotation symbol for that part. So, an error occurs and it is showing “” instead of “A“. You can use the formula below to get “A“:
    =IFERROR((INDEX(datasheet!C:C,MATCH(1,(datasheet!A:A=1)*(datasheet!B:B="a")*(datasheet!D:D="22/8/1"),0))),"-")
    To get “B“, use the formula below:
    =IFERROR((INDEX(datasheet!C:C,MATCH(1,(datasheet!A:A=2)*(datasheet!B:B="b")*(datasheet!D:D="22/8/1"),0))),"-")
    And to get “C“, you can use the formula below:
    =IFERROR((INDEX(datasheet!C:C,MATCH(1,(datasheet!A:A=3)*(datasheet!B:B="c")*(datasheet!D:D="22/8/1"),0))),"-")
    For your convenience, I have attached the excel file below with the formulas that I have used. I have used the formulas in Excel 365.
    Download Excel File
    I hope this will help you to solve your problem. If you still find any problem in your excel file, then you can send it to [email protected]. I will take a look and email you the solution.
    Thanks!

  2. Hi RICK,

    Thanks for your comment. Unfortunately, there is no similar VBA code for Microsoft Word. You need to use the VBA in Excel to get the barcode using Code 128. But you can follow the link below to use code 128 barcode font in Microsoft Word.
    https://www.exceldemy.com/print-barcode-labels-in-excel/#Step_4_Generating_and_Printing_Barcode_Labels

    I hope this will help you to solve your problem. Please let us know if you face any other issues.
    Thanks!

  3. Reply Mursalin
    Mursalin Ibne Salehin Sep 21, 2023 at 10:27 AM

    Hi HONG,

    Thanks for your comment. You can use the VBA code given below for the desired output.

    1. Copy the VBA code and paste it into the Module window.

    Function ConvertNumberToWords(ByVal MyNumber) As String
        Dim Units As String
        Dim SubUnits As String
        Dim DecimalPlace As Integer
        Dim Count As Integer
        Dim DecimalSeparator As String
        Dim UnitName As String
        Dim SubUnitName As String
        Dim SubUnitNameAlt As String
    
        ' Change these values according to your requirements
        DecimalSeparator = "."
        UnitName = "Ringgit Malaysia"
        SubUnitName = "Sen"
        SubUnitNameAlt = "Ringgit"
    
        ReDim Place(9) As String
        Place(2) = " Thousand "
        Place(3) = " Million "
        Place(4) = " Billion "
        Place(5) = " Trillion "
    
        ' Convert MyNumber to String
        MyNumber = Trim(CStr(MyNumber))
    
        ' If MyNumber is blank, return zero
        If MyNumber = "" Then
            ConvertNumberToWords = "Zero"
            Exit Function
        End If
    
        ' Find position of decimal place, 0 if none.
        DecimalPlace = InStr(MyNumber, DecimalSeparator)
    
        ' Convert SubUnits and set MyNumber to Units amount.
        If DecimalPlace > 0 Then
            SubUnits = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
            MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
        End If
    
        Count = 1
        Do While MyNumber <> ""
            TempStr = GetHundreds(Right(MyNumber, 3))
            If TempStr <> "" Then
                If Units <> "" Then
                    If Count = 1 And Len(MyNumber) > 3 Then
                        Units = TempStr & " and" & Units
                    Else
                        Units = TempStr & Place(Count) & Units
                    End If
                Else
                    Units = TempStr & Place(Count)
                End If
            End If
            If Len(MyNumber) > 3 Then
                MyNumber = Left(MyNumber, Len(MyNumber) - 3)
            Else
                MyNumber = ""
            End If
            Count = Count + 1
        Loop
    
        Dim Result As String
        Result = Trim(Units & " " & IIf(Count > 2, SubUnitNameAlt, IIf(Count = 2, SubUnitName, UnitName)))
    
        If SubUnits <> "" Then
            Result = Result & IIf(Count > 1, " and", "") & " " & SubUnits & " " & SubUnitName
        End If
    
        ConvertNumberToWords = Result
    End Function
    
    Function GetHundreds(ByVal MyNumber) As String
        Dim Result As String
        If Val(MyNumber) = 0 Then Exit Function
        MyNumber = Right("000" & MyNumber, 3)
    
        ' Convert the hundreds place.
        If Mid(MyNumber, 1, 1) <> "0" Then
            Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred"
        End If
    
        ' Convert the tens and ones place.
        If Mid(MyNumber, 2, 1) <> "0" Then
            Result = Result & " " & GetTens(Mid(MyNumber, 2))
        Else
            Result = Result & " " & GetDigit(Mid(MyNumber, 3))
        End If
        GetHundreds = Result
    End Function
    
    Function GetTens(TensText) As String
        Dim Result As String
        Result = ""           ' Null out the temporary function value.
        If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19
            Select Case Val(TensText)
                Case 10: Result = "Ten"
                Case 11: Result = "Eleven"
                Case 12: Result = "Twelve"
                Case 13: Result = "Thirteen"
                Case 14: Result = "Fourteen"
                Case 15: Result = "Fifteen"
                Case 16: Result = "Sixteen"
                Case 17: Result = "Seventeen"
                Case 18: Result = "Eighteen"
                Case 19: Result = "Nineteen"
                Case Else
            End Select
        Else                                 ' If value between 20-99
            Select Case Val(Left(TensText, 1))
                Case 2: Result = "Twenty"
                Case 3: Result = "Thirty"
                Case 4: Result = "Forty"
                Case 5: Result = "Fifty"
                Case 6: Result = "Sixty"
                Case 7: Result = "Seventy"
                Case 8: Result = "Eighty"
                Case 9: Result = "Ninety"
                Case Else
            End Select
            Result = Result & " " & GetDigit(Right(TensText, 1))   ' Retrieve ones place.
        End If
        GetTens = Result
    End Function
    
    Function GetDigit(Digit) As String
        Select Case Val(Digit)
            Case 1: GetDigit = "One"
            Case 2: GetDigit = "Two"
            Case 3: GetDigit = "Three"
            Case 4: GetDigit = "Four"
            Case 5: GetDigit = "Five"
            Case 6: GetDigit = "Six"
            Case 7: GetDigit = "Seven"
            Case 8: GetDigit = "Eight"
            Case 9: GetDigit = "Nine"
            Case Else: GetDigit = ""
        End Select
    End Function

    2. Press Ctrl+S to save the code.
    3. Now, insert the formula in Cell C3 and press Enter.
    =ConvertNumberToWords(B3)

    I hope this code will solve your problem. If you have any other queries, please let me know in the comment section.

    Regards
    Mursalin,
    ExcelDemy.

  4. Reply Mursalin
    Mursalin Ibne Salehin Sep 19, 2023 at 11:18 AM

    Hi FELIX,

    Thanks for your comment. To get your desired output, you need to use the VBA code given below. We have created a custom function to convert numbers to currency words.

    1. Copy the VBA code and paste it into the Module window.

    Function ConvertNumberToWords(ByVal MyNumber) As String
        Dim Units As String
        Dim SubUnits As String
        Dim DecimalPlace As Integer
        Dim Count As Integer
        Dim DecimalSeparator As String
        Dim UnitName As String
        Dim SubUnitName As String
        Dim SubUnitNameAlt As String
    
        ' Change these values according to your requirements
        DecimalSeparator = "."
        UnitName = "Cedis"
        SubUnitName = "Pesewas"
        SubUnitNameAlt = "Cedis"
    
        ReDim Place(9) As String
        Place(2) = " Thousand "
        Place(3) = " Million "
        Place(4) = " Billion "
        Place(5) = " Trillion "
    
        ' Convert MyNumber to String
        MyNumber = Trim(CStr(MyNumber))
    
        ' If MyNumber is blank, return zero
        If MyNumber = "" Then
            ConvertNumberToWords = "Zero"
            Exit Function
        End If
    
        ' Find position of decimal place, 0 if none.
        DecimalPlace = InStr(MyNumber, DecimalSeparator)
    
        ' Convert SubUnits and set MyNumber to Units amount.
        If DecimalPlace > 0 Then
            SubUnits = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
            MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
        End If
    
        Count = 1
        Do While MyNumber <> ""
            TempStr = GetHundreds(Right(MyNumber, 3))
            If TempStr <> "" Then
                If Units <> "" Then
                    If Count = 1 And Len(MyNumber) > 3 Then
                        Units = TempStr & " and" & Units
                    Else
                        Units = TempStr & Place(Count) & Units
                    End If
                Else
                    Units = TempStr & Place(Count)
                End If
            End If
            If Len(MyNumber) > 3 Then
                MyNumber = Left(MyNumber, Len(MyNumber) - 3)
            Else
                MyNumber = ""
            End If
            Count = Count + 1
        Loop
    
        Dim Result As String
        Result = Trim(Units & " " & IIf(Count > 2, SubUnitNameAlt, IIf(Count = 2, SubUnitName, UnitName)))
    
        If SubUnits <> "" Then
            Result = Result & IIf(Count > 1, " and", "") & " " & SubUnits & " " & SubUnitName
        End If
    
        ConvertNumberToWords = Result
    End Function
    
    Function GetHundreds(ByVal MyNumber) As String
        Dim Result As String
        If Val(MyNumber) = 0 Then Exit Function
        MyNumber = Right("000" & MyNumber, 3)
    
        ' Convert the hundreds place.
        If Mid(MyNumber, 1, 1) <> "0" Then
            Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred and"
        End If
    
        ' Convert the tens and ones place.
        If Mid(MyNumber, 2, 1) <> "0" Then
            Result = Result & " " & GetTens(Mid(MyNumber, 2))
        Else
            Result = Result & " " & GetDigit(Mid(MyNumber, 3))
        End If
        GetHundreds = Result
    End Function
    
    Function GetTens(TensText) As String
        Dim Result As String
        Result = ""           ' Null out the temporary function value.
        If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19
            Select Case Val(TensText)
                Case 10: Result = "Ten"
                Case 11: Result = "Eleven"
                Case 12: Result = "Twelve"
                Case 13: Result = "Thirteen"
                Case 14: Result = "Fourteen"
                Case 15: Result = "Fifteen"
                Case 16: Result = "Sixteen"
                Case 17: Result = "Seventeen"
                Case 18: Result = "Eighteen"
                Case 19: Result = "Nineteen"
                Case Else
            End Select
        Else                                 ' If value between 20-99
            Select Case Val(Left(TensText, 1))
                Case 2: Result = "Twenty"
                Case 3: Result = "Thirty"
                Case 4: Result = "Forty"
                Case 5: Result = "Fifty"
                Case 6: Result = "Sixty"
                Case 7: Result = "Seventy"
                Case 8: Result = "Eighty"
                Case 9: Result = "Ninety"
                Case Else
            End Select
            Result = Result & " " & GetDigit(Right(TensText, 1))   ' Retrieve ones place.
        End If
        GetTens = Result
    End Function
    
    Function GetDigit(Digit) As String
        Select Case Val(Digit)
            Case 1: GetDigit = "One"
            Case 2: GetDigit = "Two"
            Case 3: GetDigit = "Three"
            Case 4: GetDigit = "Four"
            Case 5: GetDigit = "Five"
            Case 6: GetDigit = "Six"
            Case 7: GetDigit = "Seven"
            Case 8: GetDigit = "Eight"
            Case 9: GetDigit = "Nine"
            Case Else: GetDigit = ""
        End Select
    End Function

    2. Press Ctrl+S to save the code.
    3. Now, insert the formula in Cell C3 and press Enter.
    =ConvertNumberToWords(B3)

    I hope this code will give you the desired output. If you have any other queries, please let me know in the comment section.

    Regards
    Mursalin,
    ExcelDemy.

  5. Reply Mursalin
    Mursalin Ibne Salehin Jul 3, 2023 at 10:53 AM

    Dear TAREKZHRAN,
    Thank you for your comment. Making a listbox of 14 columns and creating a search box will be complex. However, you can do that easily by modifying the code we provided with ComboBox.

    To make a list of 14 columns change the value of col_no variable in the code we have given. Here we set 2 to 4 as col_no. Suppose your column number starts from Column B to Column O, in this case, set col_no as 2 to 15.

    In the ComboBox1_Change named subprocedure change the col_header array and col_no variable like below.

    col_headers = Array("B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N","O")
    For col_no = 2 To 15


    Similarly, in UserForm_Initialize named sub procedure set col_no as 2 to 15 and ColumnCount to 14.

    To search the alphabet from left or middle or anywhere, we will replace the Left function with the InStr function. The Left function is used to search only for the first alphabet whereas Instr will help you to find it from anywhere in the text. The modified line is below.

    If InStr(1, UCase(ActiveSheet.Cells(row_no, field).Value), UCase(Me.TextBox1.Text)) > 0

    After modifying the code for TextBox1_Change named sub procedure will be like below.

    Private Sub TextBox1_Change()
    On Error Resume Next
    If Me.TextBox1.Text = "" Then
    Me.ListBox1.Clear
    Exit Sub
    End If
    Me.ListBox1.Clear
    Dim row_no As Integer
    Dim last_row_no As Integer
    last_row_no = ActiveSheet.Range("B100").End(xlUp).Row
    For row_no = 5 To last_row_no
    	letter = Len(Me.TextBox1.Text)
    	If InStr(1, UCase(ActiveSheet.Cells(row_no, field).Value), UCase(Me.TextBox1.Text)) > 0 Then
    	With Me.ListBox1
    	.AddItem ActiveSheet.Cells(row_no, "B").Value
    	.List(.ListCount - 1, 1) = ActiveSheet.Cells(row_no, "C").Value
    	.List(.ListCount - 1, 2) = ActiveSheet.Cells(row_no, "D").Value
    	End With
    	End If
    Next
    End Sub

    After applying the code, you will get results like the picture below.

    The Excel file of the solution is attached below. You can download and use it.
    Answer.xlsm
    Hope this will help you to solve your problem. Please let us know if you have other queries.
    Regards,
    Mursalin
    ExcelDemy.

  6. Reply Mursalin
    Mursalin Ibne Salehin May 11, 2023 at 11:55 AM

    Hi INGRIDA,
    Thanks for your comment. Generally, the “Ambiguous name detected: Code128” error occurs when you insert the same formula in multiple Modules in Excel. In the picture below, you can see Module 1 and Module 2 contain the same code.

    In this case, you will get the “Ambiguous name detected: Code128” error.
    To solve it, you need to keep one module and remove others. To do so, right-click on the module you want to remove. Then, select Remove Module2.

    After that, select No from the pop-up message box.

    After that, you can apply the “=Code128” formula without any error. I hope this will help you to solve your problem. Please let us know if you have any other queries.

    Regards
    Mursalin,
    ExcelDemy.

  7. Reply Mursalin
    Mursalin Ibne Salehin May 9, 2023 at 5:09 PM

    Hi RINA,
    Thanks for your comment. Per your comment, we have tried implementing the conditions in our dataset. You can see the result below:

    Here, we have used the formula below in Cell I2:
    =IFS(H2=15,CONCAT(HOUR(F2),".25"),H2=30,CONCAT(HOUR(F2),".5"),H2=45,CONCAT(HOUR(F2),".75"))
    According to one of your conditions (in case of Kate), you wanted 6:23 to be displayed as 6.25. But if you want to display time to the nearest quarter on the clock, it gives 6.5. You can check out the Excel file from the link below:
    Answer.xlsx
    It will be easier for us to give a proper solution if you send us an Excel file with the desired outputs. You can send the Excel file to my email: [email protected]. Please let us know if you have any other queries.

    Regards
    Mursalin,
    Exceldemy.

  8. Reply Mursalin
    Mursalin Ibne Salehin Apr 30, 2023 at 10:42 AM

    Dear LYDETH,

    Thanks for your comment. In this article, we have shown how to copy exact formulas in Excel both using cell reference and without using cell reference. So, you can use not only methods 12 and 13 but also methods 4, 6, 7, 8, 9, and 10 for copying the exact formulas. And if you want to copy the exact formula with relative cell address you can follow methods 1, 2, 3, 5, and 11.

    Please let us know if you have any other queries.

    Regards
    Mursalin,
    Exceldemy.

  9. Reply Mursalin
    Mursalin Ibne Salehin Apr 18, 2023 at 12:06 PM

    Dear SARAH,
    Thanks for your comment. The Variance Inflation Factor (VIF) measures the degree of multicollinearity in a set of explanatory variables in a regression model. VIF values greater than 1 indicate that the explanatory variables are correlated to some degree. However, a VIF value of infinity is not an indication of perfect correlation.

    When the R-squared value is equal to 1, it indicates that the regression model perfectly fits the data. But, setting the R-squared value to 1 does not make sense in practice as it implies that the model explains all the variation in the data, which is usually not the case.

    If the R-squared value is set to 1, the VIF becomes undefined rather than infinity. This is because the formula for VIF involves dividing by the difference between 1 and the R-squared value, and division by zero is undefined.

    So we can say, a large VIF value indicates some degree of correlation between the explanatory variables, but an infinite VIF does not necessarily indicate perfect correlation. Setting the R-squared value to 1 makes the VIF undefined rather than infinity.

    I hope this answer will help to interpret a VIF where the R-square is 1. Please let us know if you have any other queries.

    Regards,
    Mursalin,
    Exceldemy.

  10. Reply Mursalin
    Mursalin Ibne Salehin Apr 9, 2023 at 12:39 PM

    Hi KIM,
    Thanks for your comment. To explain the step-by-step solution, we used the following question here.

    “Suppose, a machine is used to produce two interchangeable products. The daily capacity of the machine can produce at most 20 units of product 1 and 10 units of product 2. Alternatively, the machine can be adjusted to produce at most 12 units of product 1 and 25 units of product 2 daily. Market analysis shows that the maximum daily demand for the two products combined is 35 units. Given that the unit profits for the two respective products are $10 and $12, which of the two machine settings should be selected?”

    This question is also stated before starting STEP 1.

    Analyzing the above question, we can find the following constraints:

    Constraint 1:
    X1+X2<=35
    Because the maximum daily demand for the two products combined is 35 units.
    Here, X1 is the quantity of Product 1 and X2 is the quantity of Product 2.

    Constraint 2:
    X1-8Y<=12

    This constraint is for Product 1. In this constraint, we have combined the conditions for both settings for Product 1. In setting 1, product 1 can be produced at most 20 units; in setting 2, product 2 can be produced at most 12 units.
    Here, Y denotes which setting is selected. If setting 1 is selected, then Y will be 1 and if setting 2 is selected, then Y will be 0.
    For example, if setting 1 is selected, then we can set Y=1. So, the equation will be:
    X1-8.1<=12
    As a result, the simplified constraint will be X1<=20 which clearly states the condition for product 1 stated in the first setting.

    Constraint 3:
    X2+15Y<=25

    This constraint is for Product 2. In this constraint, we have combined the conditions for both settings for Product 2. In setting 1, product 2 can be produced at most 10 units; in setting 2, product 2 can be produced at most 25 units.
    Here, Y denotes which setting is selected. If setting 1 is selected, then Y will be 1 and if setting 2 is selected, then Y will be 0.
    For example, if setting 1 is selected, then we can set Y=1. So, the equation will be:
    X2+15.1<=25
    As a result, the simplified constraint will be X2<=10 which clearly states the condition for product 2 stated in the first setting.

    Constraint 4:
    Y={0,1}
    The value of Y will be 0 or 1. 1 represents the selection of setting 1 and 0 represents the selection of setting 2.

    Non–Negative restriction:
    X1,X2>=0
    The quantity of the products can not be negative.

    Constraints 1, 2 & 3 are the main constraints here.

    In the Mixed Integer Linear Programming part, we have used a set of conditions as constraints where X1, X2, and X3 are integers and Y1, Y2, and Y3 are binary numbers.
    I hope this explanation of the constraints will help you. Please let me know if you have any queries.

    Regards
    Mursalin,
    Exceldemy.

  11. Hi MYLES,

    Thanks for your comment. We are very glad to know that we could be of help to you. Let us know if you have any queries.

    Good luck.

  12. Hi FREEJOJOEY,

    Sorry to hear about your problem. I am replying to you on behalf of Exceldemy. The Developer tab is not included in the ribbon by default. But you can easily add it. To add the Developer tab, please the steps of the link below:
    Get Developer Tab
    After inserting the Developer tab, follow the steps of the article. If you follow the steps correctly, you will get the scannable barcodes. You must insert the VBA using the Developer tab as stated in STEP 2 for getting the correct characters.

    Thanks!

  13. Hi STEPHEN,

    Thanks for reaching out to us. I am replying to you on behalf of Exceldemy. We will be happy to help you.
    Please send the Excel file to [email protected]. Also, mention what you want to update in the Excel file. We will try to give the solution as early as possible.

    Thanks!

  14. Hi KIERAN,

    Thanks for your comment. This is Mursalin from Exceldemy. I am not quite sure why you are getting a barcode that is not scannable. Because in my case, after changing the text to Code 128, I am getting the desired scannable barcode.

    It will be helpful for me if you provide the Excel file or an image in the comment section. Or you can send the Excel file to [email protected]. I hope I can give a solution after watching the file.
    Thanks!

  15. Hi KATIA,

    Thanks for your comment. I am replying to you on behalf of Exceldemy. To find the average, you can use the AVERAGE function. For Method 1, you can follow the steps below.
    STEPS:
    1. Firstly, select Cell F5 and type the formula below:
    =AVERAGE(C4:INDEX(C4:C12,E4))

    2. Press Enter to see the result.
    3. Secondly, select the range B3:C12.

    4. Press Ctrl + T to convert the range into a table.
    5. A message box will appear.
    6. Click OK to proceed.
    7. As a result, you will see a table like the picture below.

    8. Now, if you add Oct-15 in Cell B13, then the table and formula of Cell F5 will automatically update.

    For Method 2, you can follow the above steps and use the formula below:
    =AVERAGE(OFFSET(B4,0,0,1,-MONTH(B3)):B4)
    For Method 3, use the formula below and convert the range into a table:
    =AVERAGEIFS(C4:C17, B4:B17, ">="&B4, B4:B17, "<="&F5)
    You can also find the formulas in the workbook below:
    Workbook with AVERAGE Formulas.xlsx
    I hope this will help you to solve your problems. Please let us know if you have other queries.
    Thanks!

  16. Hi BEN,

    Thanks for your comment. I am replying to you on behalf of Exceldemy. To know more about copying formulas across cells, you can check the link below.
    Copy Formulas Across Cells
    Thanks!

  17. Hi JESSICA,

    Thanks for your comment. I am replying to you on behalf of Exceldemy. You can check the comment thread for the answer. Also, to know more about copying formulas across cells, you can check the link below.
    Copy Formulas Across Cells
    Thanks!

  18. Hi ROBYN,

    Thanks for your comment. I am replying to you on behalf of Exceldemy. To get the results across the cells, you need to drag the Fill Handle to the right side and make the necessary changes if needed. Suppose, we also need the Price along with the products. We can get the prices using some steps. Let me show you the process in the steps below.
    STEPS:
    1. Firstly, put the cursor on the bottom corner of Cell F8. It will turn into a small plus sign.
    2. Now, drag the Fill Handle to the right to Cell G8.

    3. It will show the same result and formula because the range D5:D12 is locked.

    4. Now, select Cell G8 and go to the Formula Bar.
    5. Type $C$5:$C$12 in place of $D$5:$D$12 because Column C contains the prices.
    6. Press Enter.

    7. After that, drag the Fill Handle down.

    8. Finally, you will see the prices of the products.

    To know more about copying formulas across cells you can check the article below.
    Copy Formula Across Cells
    I hope this will help you to solve your problem. Please let us know if you have other queries.
    Thanks!

  19. Hi JOJO,

    Thanks for your comment. I am replying to you on behalf of Exceldemy. You can use VBA to remove texts that have strikethrough in them. Suppose, you have some text strings like the picture below:

    Let’s follow the steps below to remove the texts with a strikethrough.
    STEPS:
    1. Firstly, go to the Developer tab and click on the Visual Basic option.

    2. Secondly, select Insert >> Module to open the Module window.

    3. Now, copy the code below and paste it into the Module window:
    Sub Remove_Strike_through_Texts()
    Dim iRng As Range, iCell As Range
    Dim iStr As String
    Dim X As Long
    On Error Resume Next
    Set iRng = Application.InputBox("Please select range:", "Microsoft Excel", _
    Selection.Address, , , , , 8)
    If iRng Is Nothing Then Exit Sub
    Application.ScreenUpdating = Fase
    For Each iCell In iRng
    If IsNumeric(iCell.Value) And iCell.Font.Strikethrough Then
    iCell.Value = ""
    ElseIf Not IsNumeric(iCell.Value) Then
    For X = 1 To Len(iCell)
    With iCell.Characters(X, 1)
    If Not .Font.Strikethrough Then
    iStr = iStr & .Text
    End If
    End With
    Next
    iCell.Value = iStr
    iStr = ""
    End If
    Next
    Application.ScreenUpdating = True
    End Sub

    4. Press Ctrl + S to save the code.
    5. After that, go to the Developer tab and select Macros.

    6. In the Macro window, select the desired code and Run it.

    7. A message box will appear.
    8. Select the range from where you want to remove the strikethrough texts.
    9. Finally, click OK to proceed.

    10. As a result, the texts with strikethrough will be removed.

    I hope this will help you to solve your problem. Please let us know if you have other queries.
    Thanks!

  20. Hi CHRIS,

    Thanks for your comment. I am replying to you on behalf of Exceldemy. We can use VBA to loop through rows of data and extract the desired data.
    For example, we will use a VBA to look for the marks of a specific student in the datasheet, copy them and paste the marks into the reportsheet. You can see the marks of all students in the picture below:

    We will copy the marks of a specific student and paste them into the reportsheet.

    For that purpose, you can use the code below:

    Sub Loop_Through_and_Extract_Data()
    Dim datasheet As Worksheet 'From where data will be copied
    Dim reportsheet As Worksheet 'where data will be pasted
    Dim StudentName As String
    Dim finalrow As Integer
    Dim i As Integer 'row counter
    'set variables
    Set datasheet = Sheet1
    Set reportsheet = Sheet3
    StudentName = reportsheet.Range("F5").Value 'Cell F5 of reportsheet contains the student name
    'clear old data
    reportsheet.Range("B5:D200").ClearContents
    'go to datasheet and start searching and copying
    datasheet.Select
    finalrow = Cells(Rows.Count, 1).End(xlUp).Row
    'loop through rows to find matching records
    For i = 2 To finalrow
    If Cells(i, 1) = StudentName Then 'if the name matches, then copy
    Range(Cells(i, 1), Cells(i, 3)).Copy 'copy columns 1 to 3 (A to C)
    reportsheet.Select 'go to reportsheet
    Range("B200").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll 'paste values in the reportsheet
    datasheet.Select 'go back to datasheet and continue searching
    End If
    Next i
    reportsheet.Select
    Range("A1").Select
    End Sub

    I have also attached the Excel file below:
    Loop Through Rows.xlsm
    I hope this will help to solve your problem. Please let us know if you have other queries.
    Thanks!

  21. Hi DAWID,

    Thanks for your comment. Sorry to hear that you are not getting the desired output. I have tried to convert 192697 and 194898 to barcodes using the same code. In my case, it worked. You can see the result below:

    I am also attaching the Excel file below:
    Answer.xlsm
    Moreover, the VBA code is not error-free. It can introduce errors sometimes. You can copy the code and paste it into a new workbook. It may help you to get the desired results.
    I hope this will help you to solve your problem. Please let us know if you have other queries.
    Thanks!

  22. Hi AHMETCAN,

    Thanks for your comment. I am replying to you on behalf of Exceldemy. To get all the desired values from the list, you need to drag the Fill Handle down to copy the formula. You can follow the steps below to get all the values.
    STEPS:
    1. Firstly, select Cell C7 and type the formula below:
    =IF(OR(COUNTIF(B7,"*"&$E$7:$E$8&"*")),B7,"")
    2. Press Enter to see the result.
    3. Thirdly, move the cursor to the bottom right corner of Cell C7, it will turn into a small black plus sign.
    4. Now, drag the Fill Handle down to Cell C16.

    5. Finally, you will see results like the picture below.

    I hope this will help you to solve your problems. Please let us know if you have other queries.
    Thanks!

  23. Hi MIKE M,

    Sorry for the late reply. I am replying to you on behalf of Exceldemy. You can mail your problem to [email protected]. Our team will take a look and try to give a solution to your problem.

    Thanks!

  24. Hi KARTHIKA,

    Thanks for your comment. To insert checkboxes without shortcuts, you can follow the steps below:

    STEPS:
    1. Go to the Developer tab and click on the Insert option.
    2. A drop-down menu will appear.
    3. You can select the checkbox from the “Form Controls” section.

    If you don’t find the Developer tab in the ribbon, then you need to add it from the Customize the Ribbon option. You will find the detail in the link below:
    https://www.exceldemy.com/add-a-checkbox-in-excel/#2_Steps_to_Add_a_Checkbox_in_Excel

    I hope this will help you to solve your problem. Please let us know if you have any other queries.
    Thanks!

  25. Hi PETE,

    Thanks for your comment. I am replying on behalf of Exceldemy. To paste the values, you can try the code below:

    Sub Copy_Data_from_Another_Workbook()
    Dim wb As Workbook
    Dim newwb As Workbook
    Dim rn1 As Range
    Dim rn2 As Range
    Set wb = Application.ActiveWorkbook
    With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
    .AllowMultiSelect = False
    .Show
    If .SelectedItems.Count > 0 Then
    Application.Workbooks.Open .SelectedItems(1)
    Set newwb = Application.ActiveWorkbook
    Set rn1 = Application.InputBox(prompt:="Select Data Range", Default:="A1", Type:=8)
    wb.Activate
    Set rn2 = Application.InputBox(prompt:="Select Destination Range", Default:="A1", Type:=8)
    rn1.Copy
    rn2.PasteSpecial xlPasteValues
    newwb.Close False
    End If
    End With
    End Sub

    Here, we have used the code of Method 1. We changed the highlighted lines to copy and paste only the values.
    I hope this will help you solve your problem. Please let us know if you have any other queries.
    Thanks!

  26. Hi EVAGGELOS,

    Thanks for your comment. I am replying on behalf of Exceldemy. Unfortunately, you can’t stop the update at a given time. But you can stop it instantly using a slightly different code and keyboard shortcut. You can follow the steps below for that purpose.

    STEPS:

    1. Copy and Paste the code in the Module window:

    Public RunWhen As Double
    Sub UpdateCell()
    RunWhen = Now + TimeValue("00:00:05")
    Application.OnTime RunWhen, "UpdateCell"
    Application.Calculate
    End Sub
    Sub StopUpdate()
    On Error Resume Next
    Application.OnTime RunWhen, "UpdateCell", , False
    End Sub

    2. Press Ctrl + S to save it.
    3. Now, press Alt + F8 to open the Macro window.
    4. Select StopUpdate from there and then, click on Options. It will open the Macro Options box.

    6. In the Macro Options box, type K in the “Shortcut Key” field.
    7. Then, click OK to proceed.

    8. Now, run the UpdateCell code.
    9. To stop updating, press Ctrl + K and the update will be stopped.

    I hope this will help you solve your problem. Please let us know if you have any other queries.
    Thanks!

  27. Hi A V S S PRASAD,

    Sorry for the late reply. I am replying to you on behalf of Exceldemy. To match the addresses, you need to use the INDEX-MATCH functions. You will find similar formulas in the articles below.
    https://www.exceldemy.com/index-match-multiple-criteria-partial-text/
    https://www.exceldemy.com/index-match-partial-match/
    I hope this will help you to solve your problem.
    Thanks!

  28. Hi TONY O’BRIEN,

    Sorry for the late reply. I am replying to you on behalf of Exceldemy. To get multiple companies, you can use the formula below in Cell G13:
    =INDEX($B$3:$B$21,SMALL(IF(ISNUMBER((SEARCH($F$13,$B$3:$B$21))),MATCH(ROW($B$3:$B$21),ROW($B$3:$B$21)), ""),ROWS($A$1:A1)),COLUMNS($A$1:A1))
    Remember, it is an array formula. You can follow the steps below to get the results.
    1. Firstly, type Diamond in Cell F13.
    2. Secondly, type the above formula in Cell G13.
    3. Press Ctrl + Shift + Enter together.
    4. After that, drag the Fill Handle down to get all 3 values.

    For more information, you can check the article below.
    https://www.exceldemy.com/index-match-multiple-criteria-partial-text/
    I hope this will help you to solve your problem.
    Thanks!

  29. Hi BRIAN WINKLE,

    Sorry for the late reply. I am replying to you on behalf of Exceldemy. To create a timestamp like the given example, you can take a look at the article below.
    https://www.exceldemy.com/create-a-timesheet-in-excel/
    I hope this will help you to solve your problem.
    Thanks!

  30. Hi JIM,

    Sorry for the late reply. I am replying to you on behalf of Exceldemy. You need to apply the formula below in Cell G16:
    =24-((C16-F16)-(D16-E16))*24
    I hope this will help you to solve your problem. Please let us know if you have other queries.
    Thanks!

  31. Hi JOE FRAZIER,

    Thanks for your comment. I am replying to you on behalf of Exceldemy. In this article, Cells I16:I22 of Column I counts the overtime for each day of a week. That means if you want to see the overtime for Monday, you need to check Cell I16. So, you can use the same formula to calculate overtime for a single day.
    I hope this will help you to solve your problem. Please let us know if you have other queries.
    Thanks!

  32. Hi FEITY LAU,

    Sorry for the late reply. I am replying to you on behalf of Exceldemy. I guess you are facing the problem because of not defining the names. Here, Cell D12 is renamed as work_hours_per_week. You can use the Name Manager in the Formulas tab to define the name. To check the defined names, follow the steps below.
    Firstly, download the practice book, go to the Formulas tab and select Name Manager.

    In the Name Manager box, you will find all the defined names.

    To apply the same formula in your new spreadsheet, you need to define the names using the Name Manager. To do that, you can follow the link below.
    https://www.exceldemy.com/excel-edit-named-range/
    Thanks!

  33. Hi DEE ZELAYA,

    Sorry for the late reply. I am replying to you on behalf of Exceldemy. Here, Cell D12 is renamed as work_hours_per_week. You can use the Name Manager in the Formulas tab to define the name. To check the defined names, follow the steps below.
    Firstly, download the practice book, go to the Formulas tab and select Name Manager.

    In the Name Manager box, you will find all the defined names.

    To apply the same formula in your new spreadsheet, you need to define the names using the Name Manager. To do that, you can follow the link below.
    https://www.exceldemy.com/excel-edit-named-range/
    Thanks!

  34. Hi CHRISTIAN,

    Thanks for your comment. I am replying to you on behalf of Exceldemy. The time difference in the above article is in Number format. That is why 8.17 doesn’t mean 8 hours 17 min. It actually means 8 hours and 10 minutes. To get the results in the desired format, you can follow the steps below.
    First of all, select Cell G16 in the dataset and type the formula below:
    =((F16-C16)-(E16-D16))
    Hit Enter to see the result.

    Select Cell G16 again, go to the Home tab, and click on the Number Format icon. It will open the Format Cells window.

    In the Format Cells window, click on the Number tab and select Time.
    Then, select 37:30:55 from the Type box.

    As a result, you will see the result in the desired format.

    I hope this will solve your problem. Please let us know if you have any other queries.
    Thanks!

  35. Dear JESSE BATES,
    Thanks for your comment. Declaring variable types is not mandatory in VBA. VBA by default assigns the necessary data type to any variable, you just need to put the values. In the given code, the timer variable contains the data type Double.
    Please let us know if you have any other queries.
    Thanks!

  36. Dear AG,
    Thanks for your comment. To avoid the Run time error in VBA, you need to add an error handling line inside the code. There are different error-handling commands in VBA. To solve the problem, add the below line after the Sub procedure.
    On Error Resume Next
    You need to add the above line inside the code like the picture below.

    To know more about handling errors in Excel VBA, you can check out the article below.
    https://www.exceldemy.com/excel-vba-on-error-resume-next/
    I hope this reply will solve your problem. Please let us know if you have any other queries.
    Thanks!

  37. Dear MICHAEL,
    Thanks for your comment. You can insert a Tab using “~009” inside the string. For example, XXXX.1TAB123456 can be written as XXXX.1~009123456. But unfortunately, it will not show two separate fields in one barcode. From my understanding, you need to use other barcode fonts for that purpose.
    Otherwise, you can look at the solution below. But this solution will not provide you with one barcode. Here, we tried to concatenate two separate barcodes in one cell. Here, we will use the dataset below.

    Let’s follow the steps below for the solution.
    ● Firstly, change the fields into barcode strings.
    ● To do so, select Cell D5 and type the formula below:
    =Code128(B5)

    ● Also, do the same for the second field.

    ● After that, select Cell B7 and type the formula below:
    =CONCAT(D5,CHAR(9),E5)

    ● Hit Enter to see the result.

    ● Now, select Cell B7 again and change the font theme to Code 128.
    ● Also, adjust the font size.

    ● Finally, you will get results like the picture below.

    I hope this solution will help benefit you to some extent. Please let us know if you face any other issues.
    Thanks!

  38. Hello CRISNA,
    Thanks for your comment. You can’t generate the text below the barcode with the code 128 font we used here. But you can use the Libre Barcode 128 Text font for that. Also, you can follow this article https://www.exceldemy.com/generate-barcode-numbers-in-excel/ to generate characters below the barcode. I hope this will help you to solve your problem. Please let us know your queries if you face any issues.
    Thanks.

  39. Hi MKM,
    Thanks for your comment. To answer your question, we can use the dataset below. It contains 3 rows of sales for Germany. The first sale happened in March and the other two sales happened in May. From this dataset, we can easily calculate the average sales for May in Germany.
    excel average if
    To calculate the average sales for May in Germany, select Cell F5 and type the formula below:
    =AVERAGEIFS(D5:D15, B5:B15,”<=5/31/2022", B5:B15,">=5/01/2022″,C5:C15,”Germany”)
    Press Enter to see the result.
    excel average if
    I hope this will help you to solve your problem. Please let us know your queries if you face any issues.
    Thanks.

  40. Dear NEIL,
    Thanks for your comment. Code 128 barcode font has 106 unique representations and supports standard ASCII characters. Unfortunately, Õ and Œ – these two characters are not supported by code 128 barcode font. But you can try other barcode fonts to solve this issue. You can use the free online barcode generator for that purpose. I hope this will help you to solve your problem.
    Thanks.

  41. Dear CHRIS,
    Thanks for your comment. Excel omits the leading zero by default. So, it’s not possible to get the desired barcode.
    • To solve this, you must add an apostrophe (‘) in front of the leading zero.
    • So, select Cell C5 in the VBA sheet of the workbook.
    • Type ‘02628107336750 in Cell C5.
    • Press Enter.
    • You will get your results in Cell D5.
    I hope this will help you to solve your problem.
    Thanks.

  42. Hello, Abu!
    Thanks for your comment. Unfortunately, it is not possible to show the expected end date dynamically after each pre-payment. Because the dataset changes dynamically after a pre-payment. But you can find the expected payment date after each month using the formula below. For the dataset, we have used in this article, you can type the formula in Cell H8:
    =DATE(YEAR(H7),MONTH(H7)+(12/$J$15),DAY(H7))
    Here, Cell H7 contains the loan starting date and Cell J15 contains the number of yearly payments.
    You can also take a look at this article https://www.exceldemy.com/student-loan-payoff-calculator-with-amortization-table-excel/ for an explanation of the formula. I hope this will help you to solve your problem.
    Thanks!

  43. Hello, Dom!
    Thanks for your feedback. If you are facing any problems generating code 128 barcode font in Excel, then you can ask here. We will try to reply to you as soon as possible.
    Thanks!

  44. Hello, Bimen!
    Sorry to hear that you are facing problems converting minutes to decimal numbers. You can send your excel file to [email protected] and we will try to give a solution to your problem.
    Thanks!

  45. Hi BIPLAB,
    Thanks for your query. You can build a formula with the nested CONCAT function. In that case, you don’t need any helper column. For the dataset we have discussed in the article, the formula will be:
    =CONCAT(CONCAT(B5,” “,C5,” “,D5),”,”, CONCAT(B6,” “,C6,” “,D6),”,”,CONCAT(B7,” “,C7,” “,D7),”,”,CONCAT(B8,” “,C8,” “,D8))
    You can also use the VBA code below. To apply this, you need to select the rows of the first column of the dataset and then run the code from the Macro window.

    Sub Merge_Rows_with_Comma()

    Dim iSelection As Range
    Dim iRow As Range
    Dim iCell As Range
    Dim iStr As String

    Application.ScreenUpdating = False
    On Error Resume Next
    Set iSelection = Intersect(Selection, ActiveSheet.UsedRange)

    For Each iRow In iSelection.Rows
    For Each iCell In iRow.Cells
    iStr = iStr & ” ” & VBA.Trim$(iCell)
    Next iCell
    iRow.ClearContents
    iRow.Cells(1, 1).NumberFormat = “@”
    iRow.Cells(1, 1) = Mid(iStr, 2)
    iStr = vbNullString
    Next iRow

    For Each iCell In iSelection
    iStr = iStr & “,” & VBA.Trim$(iCell)
    Next

    With ActiveWindow
    .Selection.ClearContents
    .Selection(1, 1).NumberFormat = “@”
    .Selection(1, 1).Value = Mid(iStr, 2)
    End With
    End Sub

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo