User Posts: Rafiul Haq
0
Preparing Bond Amortization Schedule in Excel (with Easy Steps)
0

We can perform various finance related tasks in Microsoft Excel very easily. In this article, you will learn about how to prepare a bond amortization schedule ...

0
How to Create a Balance Sheet for Small Business in Excel
0

The income statement, balance sheet, owner's equity statement, and cash flow statement are the four main types of financial statements. The balance sheet is ...

0
Revised Schedule 3 Balance Sheet Format in Excel with Formula
0

In this article, we will show you quick steps on how to create a revised Schedule 3 balance sheet format in Excel with formula. Download Practice ...

0
How to Create Pro Forma Financial Statements in Excel
0

In this article, we will show you how to create pro forma financial statements in Excel. We will project three years of financial statements for a startup ...

0
How to Create Format for Projected Financial Statements in Excel
0

Projecting the financial statements can be a daunting task. Numerous systematic and unsystematic risks exist that could prevent the projection. However, we ...

0
How to Prepare Financial Statements from Trial Balance in Excel
0

In this article, we will show you how to prepare three financial statements from an adjusted trial balance in Excel. Before everything else, let us briefly ...

0
How to Use SUMIFS When Cells Are Not Equal to Multiple Text
0

We can use conditional summation using the SUMIF and SUMIFS functions in Excel. The SUMIFS function is available from Excel version 2010. This function can ...

0
How to Set Option Button Value in Excel VBA (4 Easy Steps)
0

The Option button or radio button is an important feature in the UserForm. We can set the option button value and use VBA macro to execute various commands. In ...

0
How to Create a Recurring Monthly Schedule in Excel
0

Some events may happen frequently in your life. You can keep track of it easily with the help of Excel. In this article, we will show you two quick steps to ...

0
[Solved:] Long Numbers Are Displayed Incorrectly in Excel
0

Working with long numbers (more than 11 digits) can be a problem in Excel. There are some auto formatting options in the program to keep the spreadsheet clean. ...

0
Skip to Next Result with VLOOKUP If Blank Cell Is Present
0

Oftentimes, we have blank values in our dataset. If there is no value in the first match and the exact match condition is set, the VLOOKUP function will return ...

0
How to Open File Dialog Default Folder with Excel VBA (3 Ways)
1

VBA can automate many mundane tasks in Excel. It can save a lot of time. Knowing the default folder location allows us to be certain of where our file will be ...

0
How to ReDim Preserve 2D Array in Excel VBA (2 Easy Ways)
0

An array is a variable that keeps the same kinds of data. If there is only one row or one column of data, then it is known as a one-dimensional array. However, ...

0
How to Flip Data Upside Down in Excel (4 Ways)
0

Often, we need to mirror data in Excel. This article will show you 4 quick ways to flip data in Excel upside down. We will implement two formulas, one command, ...

0
How to Add Sheet with Name from Cell Using Excel VBA
0

We can save valuable time by using the Excel VBA Macro. Often, we need to add new sheets and name them from a cell manually, but we can do it automatically by ...

Browsing All Comments By: Rafiul Haq
  1. Hello Cielo. Thank you for your question. I have tested the function on Excel 365, 2021, and 2013 versions. On first two version, you simply press ENTER and the formula will be spilled. However, in Excel 2013 if you press that, it will only show the first value.

    Here, we have typed the formula in cell C5.

    Then, pressed CTRL+SHIFT+ENTER. But, only the first value is seen.

    To solve this, you need to follow these steps:
    1. Select the cell ranges C5:C8 first.

    2. Type the formula =MODE.MULT(B5:B11)

    3. Press CTRL+SHIFT+ENTER. So, it will show all the mode values. Additionally, we have selected four cells, but there are only three outputs. Therefore, there is a “#N/A” for that reason.

    Solution to the Issue: MODE.MULT Function Not Working

  2. Hello Andrew, you can download this Excel file which allows users to enter any amounts (including zero payments). Here, you need to input your value in Total Periods cell.

    Zero Payments

  3. you can download this Excel file which allows users to enter any amounts (including zero payments). Moreover, you can see amount to be paid at the last balance, which is 11,368.

    Balloon Payment

  4. Hello Kenneth Mayfield. you can download this Excel file which allows users to enter any amounts (including zero payments).

    Zero Payments

  5. Hello, Michael Cooper. you can download this Excel file which allows users to enter any amounts (including zero payments).

    Zero Payments

  6. We are glad that this article helped you.

  7. Hello Gerry Candeloro, this works perfectly on our end.

    Comment Solution

    Here the interest is compounded annually, and the payment due is Monthly and Bi Weekly.

  8. Hey Buck, you can follow this article for no compounding interest.

  9. Hi Sohel! Your problem is not clear to us. You can email us the problem [email protected]

  10. Hello Elizabeth D Jones, the template was available on the top of the article. If you still cannot find the link, you can download from here.

  11. You can split the payment schedule into desired quarters and calculate the payments using that quarters’ interest rates.

  12. Different credit cards have different grace periods. You can send us your Excel file and we will look into it.

  13. Hello Ravneet, you can follow this article and adjust your payments on the “Extra Payment” column.

  14. Hi Lisa. You can use the YEARFRAC function to calculate the days difference in fraction of that year. Then, you can multiply the result with the annual interest payment (in this case, scheduled monthly payment*12) to include the pro-rata interest.

  15. I have modified the VBA code from the second method to handle blank cells for your dataset. Please, check that.

  16. Hello Amilia. Thank you for your question. You can use the following VBA code to ignore blank cells within the range.

    Sub URLPhotoInsert2()
    Dim cPhoto As String
    Dim cPicture As Picture
    Dim cRange As Range
    Dim cItem As Range
    Set cRange = Range("C5:C10")
    For Each cItem In cRange
        cPhoto = cItem.Offset(0, -1)
        If cPhoto = "" Then GoTo line33
            Set cPicture = ActiveSheet.Pictures.Insert(cPhoto)        
            With cPicture
                .ShapeRange.LockAspectRatio = msoFalse
                .Width = cItem.Width
                .Height = cItem.Height
                .Top = Rows(cItem.Row).Top
                .Left = Columns(cItem.Column).Left
                .Placement = xlMoveAndSize            
            End With
    line33:
             cPhoto = ""        
        Next
    End Sub

    Solution to Blank Cell Problem

  17. Hello Missy, If you change the list it will change the results, both are the same. Your question “can I then edit the column with the book title in it?” is not clear to us. It will be better for us, if you create a sample Excel file of what you want and share it with us. Thanks.

  18. Hi, you can learn about how to hide source data from this article.

  19. Thank you Lyn for your comment.

    You can try the ZIP method to remove password from the Excel file.

    This may not work with Excel 365 version and you will need to use an “Excel password recovery utility”.

  20. Thank you Chelsea for your comment. We have checked the code and it’s working perfectly on our end. Here, we have set the date as

    =DATE(2022,8,21)

    and 1 as the increment.

    VBA Code Working OK

    Did you follow the steps correctly? If yes, then you can send your Excel file and we can take a look into that.

  21. Thank you Brian for your comment.

    Actually, we believe you are right. However, in Excel the NORMDIST function shows the probability mass function when it is set to False. We have kept the original naming as it.

    NORMDIST Function

    Moreover, if you go through the official documentation, you will notice it is written as “density function” in the latter part. So, chances are it may be displayed as ”probability mass function” but actually it is calculating the ”continuous values”.

    NORMDIST Function Official Doc

    • You can modify the code to convert Pipe Delimited CSV files to XLSX using the Command Line.
    '======================================
    ' Convert CSV to xlsx Command Line
    '======================================
    
    srccsvfile = Wscript.Arguments(0) 
    tgtxlsfile = Wscript.Arguments(1)
    
    'Create an Excel worksheet where you want to place the file
    
    On Error Resume Next 
    'Set a variable and define
    Set ObjectiveFileExcel = GetObject(,"Excel.Application")
    
    'If condition for creating new instance if the find is not found
    If Err.Number = 429 Then '> 0
    
    'Set a command to create new instance 
    Set ObjectiveFileExcel = CreateObject("Excel.Application")
    End If
    
    ObjectiveFileExcel.Visible = false
    ObjectiveFileExcel.displayalerts=false
    
    'Give a command to import CSV into Excel
    Set ImportWbk = ObjectiveFileExcel.Workbooks.open(srccsvfile)
    Set ImportWbk1 = ImportWbk.Worksheets(1)
    
    'Code for Pipe Delimiter
    With ImportWbk1.QueryTables.Add("TEXT;" & srccsvfile, ImportWbk1.Range("$A$1"))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "|"
        .TextFileColumnDataTypes = Array(1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh False
    End With
    
    'Set a command to adjust column widths
    Set ObjectiveRng = ImportWbk1.UsedRange
    ObjectiveRng.EntireColumn.Autofit()
    ' ObjectiveFileExcel.Columns(intColumns).AutoFit()
    'Next
    
    'Give TRUE statement to set the column Headings Bold
    ObjectiveFileExcel.Rows(1).Font.Bold = TRUE
    
    'Give TRUE statement to freeze panes for the Header Row
    With ObjectiveFileExcel.ActiveWindow
    .SplitColumn = 0
    .SplitRow = 1
    End With
    ObjectiveFileExcel.ActiveWindow.FreezePanes = True
    
    'Apply AutoFilter to Heading Row
    ObjectiveFileExcel.Rows(1).AutoFilter
    
    'Make Header row Interior Cyanide
    ObjectiveFileExcel.Rows(1).Interior.ColorIndex = 31
    
    'Save the worksheet
    ImportWbk1.SaveAs tgtxlsfile, 51
    
    'Make free the Lock on worksheetsheet
    ObjectiveFileExcel.Quit()
    Set ImportWbk1 = Nothing
    Set ImportWbk = Nothing
    Set ObjectiveFileExcel = Nothing
    • Then, type this on the PowerShell and hit ENTER.
    .\convert-csv-to-excel C:\Users\Admin\Desktop\source.csv C:\Users\Admin\Desktop\converted.xlsx
  22. You can do that by following these three steps:
    1. You can insert any files using the Insert > Object command.

    2. Save as PDF and Email Attachment.

    3. Apply this code on a VBA Button.

  23. Hey Ash, thank you for your question.

    Suppose, we have these three items in cell Z2 as a Dropdown List:
    X
    Y
    Z
    Then, if we choose X, then it should display as a list in AA2 cell:
    P
    Q
    R

    Now, to do that, select cell range that contains PQR (eg. T1:T3), then use Name Range
    PQR as X. Similarly, name the ranges as Y and Z for their corresponding cell ranges.

    Next, use Data Validation on cell AA2.
    Allow: List
    Source: =INDIRECT(Z2)

    Then, it should do what you want.

    TLDR; Use Name Range and INDIRECT function inside Data Validation.

  24. Thank you MOE for your wonderful question. To do this you need to apply the following Custom Cell Format.

    [<=1000000]  #,"K";[>1000000]   #,,"M"
  25. You can do this by combining IF function with your formula.
    Create multiple tables for your price information for the years. Refer the year to a cell and the use it to change the VLOOKUP’s “table_array”.

    For example the year is on cell F4,

    now you can type, =IF(F4=2023,IFERROR(VLOOKUP(B5,’Material Pricing’!B5:D7,3,0),””),IF(F4=2024,IFERROR(VLOOKUP(B5,’Material Pricing’!B13:D15,3,0),””),””))

    If the year is 2023, then the “table_array” will be B5:D7
    IF 2024, it will be B13:D15.
    Else it will return a blank.

    To know more about this, you can read this.

  26. Thank you for your reply, Thor.

  27. Thank you for your question. You need to use Underscore (“_”) between two words in the Name Range.

  28. Thank you for your question.

    In my second code, instead of “ActiveSheet.Pictures.Insert” you can try using “ActiveSheet.Shapes.AddPicture”, which should embed the images in the Excel file and it will not disappear.

    the modified VBA code from the second method will be:

    Sub URLPhotoInsert2()
    
    Dim cPhoto As String
    Dim cPicture As Shape
    Dim cRange As Range
    Dim cItem As Range
    
    Set cRange = Range("C5:C10")
    For Each cItem In cRange
        cPhoto = cItem.Offset(0, -1)
        If cPhoto = "" Then Exit Sub
            Set cPicture = ActiveSheet.Shapes.AddPicture(cPhoto, _
            msoFalse, msoTrue, Columns(cItem.Column).Left, _
            Rows(cItem.Row).Top, cItem.Width, cItem.Height)
            
            With cPicture
                .LockAspectRatio = msoFalse
                .Placement = xlMoveAndSize
            End With
            
        Next
    
    End Sub
  29. You are welcome. I am glad that this article was useful to you.

  30. You need to change Google Drive’s Shared URL when inputting it into the cell.
    For example, if I share an image the URL will be -> “https://drive.google.com/file/d/18r34oAVY8-bicTmf3CaTIumuHp_hqZxH/view?usp=sharing”.
    Then, I need to change it to -> “https://drive.google.com/uc?export=view&id=18r34oAVY8-bicTmf3CaTIumuHp_hqZxH” (putting the values after d/ inside the id values of the URL).

    And for your second question, I think the URL is not correct, if I remove the image resolution info from the URL, then it works perfectly in my second code.

    Your URL -> “https://www.exceleinfo.com/wp-content/uploads/2022/06/Referencias-3D-en-Excel-1024×477.png”.
    it should be -> “https://www.exceleinfo.com/wp-content/uploads/2022/06/Referencias-3D-en-Excel.png”.

  31. According to your example, Set cRange = Range(“C5, C8, C11”)
    Then, you’ll need to change the height & width to MergeArea
    .Width = cItem.MergeArea.Width
    .Height = cItem.MergeArea.Height

    Full Code >

    Sub URLPhotoInsert2()
    Dim cPhoto As String
    Dim cPicture As Picture
    Dim cRange As Range
    Dim cItem As Range
    Set cRange = Range("C5, C8, C11")
    For Each cItem In cRange
        cPhoto = cItem.Offset(0, -1)
        If cPhoto = "" Then Exit Sub
            Set cPicture = ActiveSheet.Pictures.Insert(cPhoto)
            With cPicture
                .ShapeRange.LockAspectRatio = msoFalse
                .Width = cItem.MergeArea.Width
                .Height = cItem.MergeArea.Height
                .Top = Rows(cItem.Row).Top
                .Left = Columns(cItem.Column).Left
                .Placement = xlMoveAndSize
            End With
        Next
    End Sub

    The output will be this: https://ibb.co/KXHVzSC

  32. Thank You.

ExcelDemy
Logo