Latest Posts From Sajid Ahmed
In this article, you’ll learn 6 suitable examples of how to use Excel Calculation Options. The examples include changing the Calculation Options to ...
Sajid Ahmed
In this article, you’ll learn how to export an Excel file to a CSV file using 4 methods. The methods include exporting only the active sheet using the Save As ...
In this article, you’ll learn how to use the COUNTIF function in Excel with multiple criteria in different scenarios with 4 suitable examples. The examples ...
In this Excel tutorial, you will get a list of useful Excel formulas for your daily use while working. We’ll use relevant practical datasets to give you a ...
What Is GST Reconciliation in Excel? GST (Goods and Services Tax) is a tax applied to items you purchase or services you use. It’s added to the cost ...
In this article, you’ll learn how to do three way reconciliation in Excel with 5 simple steps. You’ll also learn what is three way reconciliation before ...
This is an overview of Excel VBA tools used to refresh data connections. Create Data Connections between Excel Files The original dataset is ...
In this Excel tutorial, you'll learn -What is a running total -What are the uses of running total -How to calculate running total in Excel -And how to ...
In this Excel tutorial, you'll learn how to - Make a drop-down list in Excel (dependent, editable, and dynamic) - Input a message and error alert in the list ...
With the help of Excel's SUMIF function, users can sum a range of numbers in accordance with specific criteria. However, there are times when the SUMIF ...
Suppose you have a list of your daily or monthly transactions. You want to know in which transaction category you are spending the most. You can get this ...
In the following image, you see two columns having similar names. Column E shows which names are exactly the same and which are not. In this article, ...
In this Excel tutorial, you will learn how to get 2 decimal places without rounding in Excel. Getting 2 decimal places without rounding in Excel can be done ...
Hello,
Thanks for your comment.
There was a small mistake in the formula. That’s why it is giving a Name error. The article and workbook are corrected and updated accordingly. If you have other queries let us know in the comment.
Regards,
Sajid Ahmed
Exceldemy
Hello,
Thanks for your comment.
The formulas are all right. They are working very nicely in our case. You have to insert the formulas carefully in the conditional formatting. Make sure you have put the cell references either absolute or relative correctly. Or if you can provide more information about your dataset then we could help.
If you have other queries let us know in the comment.
Regards,
Sajid Ahmed
Exceldemy
Hello TOM,
Thanks for your comment.
Suppose, you have the following dataset with data in a single column in cells B5:B8. You want to add single quotes and separate them with a comma in cell B11. So, use the following formula with the CONCATENATE function in cell B11:
=CONCATENATE(“‘”,B5,”‘”,”, “,”‘”,B6,”‘”,”, “,”‘”,B7,”‘”,”, “,”‘”,B8,”‘”)
Press Enter to get the desired output.
If you have other queries let me know in the comment.
Regards,
Sajid Ahmed
Exceldemy
Hello DRIN,
Thanks for your comment.
If you want to sum Notebook from India up to France and use drop-down for the countries, then use the following formula in cell C13:
=SUM(INDEX($B$5:$G$8,MATCH(C10,$B$5:$B$8,0),MATCH(C11,$C$4:$G$4,0)+1):INDEX($B$5:$G$8,MATCH(C10,$B$5:$B$8,0),MATCH(C12,$C$4:$G$4,0)+1))
It’ll give the desired result.
Change the countries to China and Germany from the drop-down and the total value will be updated.
If you have other queries let me know in the comment.
Regards,
Sajid Ahmed
Exceldemy
Hello ALEXANDER,
Thanks for your comment.
First, calculate the amortization schedule for the original loan. Then, calculate the amortization schedule for the additional loan separately. Now that you have the monthly payments for both loans, you can combine them to create a single amortization schedule. You’ll need to add the monthly payments together for each month and subtract this total from the outstanding balance.
Repeat this process for each month until both loans are fully paid off. The combined amortization schedule will show the monthly payments and remaining balances as you pay off both loans simultaneously.
If you have other queries let me know in the comment.
Regards,
Sajid Ahmed
Exceldemy
Hlw Jon S M,
Thanks for your comment.
If a customer has more than one invoice with different dates, you have to include multiple rows for that customer in the dataset. Each row will represent a separate invoice entry.
The information related to that customer, such as their name, will be repeated in each row for their respective invoices. But the dates, invoice ids and invoice amounts will be different. The rest of the process is similar with this article.
If you have other queries let me know in the comment.
Regards,
Sajid Ahmed
Exceldemy
Hello Hermione,
Thanks for your comment.
The “Run-time error ‘9’: Subscript out of range” error typically occurs when VBA code tries to reference a worksheet or object that doesn’t exist in the current workbook. In your case, the error is likely occurring because there is no worksheet named “all books” in your Excel workbook.
To fix this issue, you need to ensure that the worksheet name you’re trying to reference (“all books”) exactly matches the name of a worksheet in your workbook. If the worksheet name is different or contains typos or extra spaces, you will encounter this error.
If you have other queries let me know in the comment.
Regards,
Sajid Ahmed
Exceldemy
Hlw Terrisa,
Thank you very much for your comment. The right formula for your need is given below-
=IFERROR(MIN((DATEDIF(W6,TODAY(),”d”)+1)/(DATEDIF(W6,X6,”d”)+1), 1), 0)
Here, we add the MIN function to cap the calculated percentage to 100%. If the calculated percentage is greater than 100%, this function will return 100%, capping the percentage. If the calculated percentage is less than or equal to 100%, it will return the calculated percentage as it is.
If you have other queries let me know in the comment.
Regards,
Sajid Ahmed
Exceldemy
Thank you very much for your valuable comment. You are right. The ASCII character 160 is different than character 32. If you have any cell containing the ASCII character 160 non-breaking space then the TRIM function described in method 1 won’t work.
However, you can use the combination of the SUBSTITUTE and CHAR functions then. Suppose you have a value in cell A1 of your dataset containing the ASCII character 160 non-breaking space. Then you can use the following formula to remove unwanted spaces-
=SUBSTITUTE(A1,CHAR(160), “”)
After using this formula you can apply the Remove Duplicates button.
Regards
Sajid Ahmed
ExcelDemy
Hello Ayodeji Aboderin, thanks for reaching out. You are right, there was an error. Now, the article is updated accordingly. Please let us know if you have any other queries.
Regards
Sajid Ahmed
Exceldemy