# Md. Nafis Soumik

Md. Nafis Soumik graduated from Bangladesh University of Engineering & Technology, Dhaka, with a BSc.Engg in Naval Architecture & Marine Engineering. In January 2023, he joined Softeko as an Excel and VBA content developer, contributing 50+ articles on topics including Data Analysis, Visualization, Pivot Tables, Power Query, and VBA. Soumik participated in 2 specialized training programs on VBA and Chart & Dashboard designing in Excel. During leisure, he enjoys music, travel, and science documentaries, reflecting a diverse range of interests.

## Designation

Excel & VBA Content Developer at ExcelDemy in SOFTEKO.

## Education

B.sc in Naval Architecture & Marine Engineering, Bangladesh University of Engineering & Technology

## Expertise

Content writing, VBA, C++, Python, MATLAB, MS Office, AutoCAD.

## Experience

• Industrial Training in Khulna Shipyard Limited
• Technical Content Writing
• Design of an 87m long Offshore Supply Vessel (OSV)
• A Comparative Linear Buckling Analysis of Different Isotropic Plates with and without Stiffeners for Different Boundary Conditions.

## Summary

• Currently working as an Excel & VBA Content Developer.
• Started technical content writing of Excel & VBA in January 2023.

## How to Create an Inventory Cost Carrying Calculator in Excel – 3 Steps

This is an overview of the final output.   Inventory Carrying Cost Formula Inventory Carrying Cost = (Average Inventory x Holding ...

## How to Calculate Upper and Lower Limits in Excel (2 Methods)

In this article, we will learn how to calculate upper and lower limits in Excel using 2 different methods. We will use  the sales data of ten different ...

## How to Use Electricity Bill Calculation Formula in Excel – 2 Methods

The dataset shown below contains the current meter reading, the past meter reading, and the consumed units of electricity of ten different households located ...

## How to Convert Monthly Interest Rate to Annual in Excel

The article will walk you through two straightforward Excel procedures to convert a monthly interest rate to an annual rate. We'll use the following sample ...

## How to Use What-If Analysis in Excel (Definition and Types)

In the following dataset, we have a two-variable what-if analysis on a direct mail profit model. One variable is the response rate, which varies through the ...

Browsing All Comments By: Md. Nafis Soumik
1. Hi Kevin!
It brings me great joy to know that you found our blog helpful. You are most welcome. As for your query, you can use the following formula: `=INDEX(D4:D12,MATCH(1,(F4=B4:B12)*(G4=C4:C12),0))`
To be more clear about the cell references look at the image below:

Regards,
Nafis
ExcelDemy

2. Hi MAHMUD,

Thank you for your valuable suggestion. I updated the formula for calculating electricity bill with Variable Unit Price (Slab). Hope you find this article useful.

Regards,
ExcelDemy

3. Hi Liam,
Thank you for taking the time to comment on our blog and for suggesting a shorter method for the task that is demonstrated. We appreciate your input and always welcome new ideas.

After carefully considering your formula, I noticed there was a backslash “\” missing from the col_delimiter argument of the TEXTSPLIT() function inside the formula. To ensure the best results, modify your formula like this: `=CHOOSECOLS(TEXTSPLIT(A1,"\"),-1)` (Assuming the full path address is inside the cell A1).

We value your engagement and encourage you to keep sharing your thoughts and ideas. It’s through constructive discussions like this that we can all learn and improve. Thanks again for your comment!
Regards,
ExcelDemy.

Md. Nafis Soumik Oct 3, 2023 at 9:22 AM

Hi Mahmoud,
You can create a half doughnut chart to show the correct percentages with data labels by following the steps mentioned in this article. Only exception is you will need a different data table with the yearly revenues converted into percentages of the total.
You can do this by applying the following formula inside any cell: `=C5/\$C\$10`. Use fill handle to populate the dataset and keep the percentage column into percentage format. Now follow the exact steps described in this article. Upon finishing check the Data Labels option from Chart Element. Your half doughnut chart with percentage Data labels is now ready.

I hope it solves your problem.

Regards,
ExcelDemy.

5. Hello JK,

Which formula are you talking about?
Do you have a formula for yourself that you want to convert?
Or, Do you want to convert any of the formulas from the post?

Please let us know. We would be happy to help.

Regards
Exceldemy

6. Hi Jenn,
Based on your comment, it seems you are looking for a way to create a spreadsheet that displays different tables based on the selected company.
To address your requirement, one approach is to use Excel’s “Data Validation” feature combined with “Index” and “Offset” functions. Here’s a potential step-by-step solution:
1. Set up your spreadsheet with separate tables for each company, each in a different range of cells.
2. Create a list of company names (e.g., in a dropdown) where the user can select the desired company.
3. Assign data validation to the cell where the user selects the company, limiting the input to the list of company names.
4. Next, use the “Index” and “Offset” functions to display the corresponding table based on the selected company.

Here’s an example of how this could work:
1. Create separate tables for each company, each in a different range of cells (e.g., Company A in cells A1:D10, Company B in cells A15:D24, Company C in cells A29:D38).
2. Create a dropdown list of company names (e.g., in cell A50) using Excel’s Data Validation feature.
3. Use the “Index” and “Offset” functions to retrieve the appropriate table based on the selected company. For example, in cell A55, you could use the following formula:
`=INDEX(\$A\$1:\$D\$10, OFFSET(\$A\$1:\$A\$10, MATCH(\$A\$50,\$A\$1:\$A\$10,0)-1, 0, ROWS(\$A\$1:\$A\$10), COLUMNS(\$A\$1:\$D\$10)))`
This formula will retrieve the table for the selected company dynamically.
Now, when you select a company from the dropdown list in cell A50, the corresponding table will be displayed in cell A55 and automatically update based on the selection.
Please note that the specific ranges and formulas may need to be adjusted based on the actual structure and layout of the spreadsheet. However, this approach should provide a starting point to achieve the desired functionality of displaying different tables based on the selected company in Excel.

Regards,
ExcelDemy

Md. Nafis Soumik Jul 2, 2023 at 2:06 PM

Hello Kumar,
Both the formulas you mentioned work fine and generate same results. Try again. If it still does not work, please send us your Excel file at [email protected]
And yes. There is a simpler formula to find out the original price directly without calculating the GST amount. Use this formula inside cell C7: Original price = C4/(1+C5)

Thank you.
Regards,
Exceldemy

8. Hello Ed McCann,
It’s possible that the Pivot table is referencing a range of cells that includes data outside of the intended range. Here are a few steps you can try to troubleshoot the issue:

1. Check the source data range: Make sure that the Pivot table is referencing the correct range of cells that contain your data. Select any cell inside the Pivot table, go to the “Analyze” or “Options” tab in the ribbon, and look for the “Change Data Source” or “Select Data” button. Clicking on this button will show you the range of cells that the Pivot table is using as its data source.

2. Check for hidden data: It’s possible that there is hidden data in the source data range that is being included in the Pivot table. Select the source data range, go to the “Home” tab in the ribbon, and click on the “Format” dropdown. From here, click on “Hide & Unhide” and then “Unhide Rows/Columns” to reveal any hidden data.

3. Refresh the Pivot table: If none of the above steps work, try refreshing the Pivot table. Select any cell inside the Pivot table, go to the “Analyze” or “Options” tab in the ribbon, and click on the “Refresh” button. This will recalculate the Pivot table based on the current data source.