User Posts: Rubayed Razib Suprov
How to Do Linear Programming with Sensitivity Analysis in Excel

Linear programming has been used to determine the optimal value that will satisfy all of the constraints and conditions specified by the problems. Although the ...

Excel ISNUMBER Not Working (3 Reasons with Solutions)

We have to determine whether a cell contains a number value or not in different parts of the excel formulation. But in a lot of unfortunate cases, those ...

How to Return TRUE If Cell Contains Text in Excel (8 Easy Ways)

Sometimes we need to determine whether a cell contains text or not. Because that cell might be needed for other purposes where the cell format needs to be ...

Insert Drop Down Calendar in Excel Without Date Picker

We need to use the calendar in Excel on daily basis. But for every reason, using a date picker calculator can be quite a hassle. This type of customized ...

How to Use Format Painter Multiple Times in Excel (5 Easy Ways)

We use format painter daily in countless times. But the main issue with the Format Painter is to it gets deactivated after one cell formatting. In this ...

How to Calculate Commission in Excel Using IF Function

The Calculation of commission is one of the basic tasks of Excel applications for a long period of time. Calculating commission based on the sales level is ...

Excel DSUM vs SUMIF Functions (2 Suitable Examples)

We normally need to evaluate the summation of values by criteria. Which is one of the most commonly used functions in the world. So, to carry out this type of ...

How to Calculate Realized Volatility in Excel (with Easy Steps)

Which type of stock we should invest in and how the performance of the stocks would be, have been some burning questions for millennia. Investors will look to ...

How to Make a Picture Catalog in Excel (with Easy Steps)

A picture catalog is an attractive tool to showcase your product. Moreover, it can also help to give audiences a clear idea about the company overview. An ...

How to Close Queries and Connections Pane in Excel

Closing connections and query pane can be troublesome, we can do this by simple process or it can be done by other means. In this article, we will discuss 2 ...

How to Join Pivot Tables in Excel (with Easy Steps)

Pivot Table is an amazing feature of Excel, where we can show our large dataset in a summary according to our requirements. Sometimes, we need to merge two ...

How to Make a Calculator in Excel (with Easy Steps)

The calculator is a part and parcel of our daily life. We can use the calculator directly inside Excel without switching to other applications or manual ...

How to Calculate Coefficient of Skewness in Excel

Coefficient of skewness is a very important tool to understand a dataset’s main notion. In which direction is the dataset actually lean in which may lead to ...

How to Do NPV Break Even Analysis in Excel (with Easy Steps)

NPV Break Even analysis is one of the most widely used tools to understand a business procedure and important indicator to shape future investment. In this ...

How to Show Intersection Point in Excel Graph (3 Effective Ways)

Solving equations is one of the most useful mathematical functions as it gives us an immense opportunity to solve many real-life problems. In this article, we ...

Browsing All Comments By: Rubayed Razib Suprov
  1. Reply Avatar photo
    Rubayed Razib Suprov Aug 8, 2022 at 12:38 PM

    Thanks for your question. Actually, you can approach this problem in two separate ways. One is directly calculating the significant value or using a chart. For the chart, you need to calculate the T value first, and then you will calculate the p-value. Using them, you can calculate the critical value from a chart available online.
    1. The formula for calculating the T value is, t=r_s×√((n-2)/(1-r_s^2 ))
    Where r_s is the Spearman correlation value.
    n is the no of entry
    The Excel formula would be in our case =E17*SQRT((E16-2)/(1-E17^2))
    2. The formula for significant value, p =T.DIST.2T(ABS(calculated t value),n-2)
    3. To calculate the critical value, you need to have a critical value chart. Using the p-value and the n (Number of entries), from the chart, you need to get the critical value.
    Chart for Calculating the Critical Value for Significant Assessment
    4. You may be needed to interpolate the critical values as you may not have the exact p or n values. If your correlation value>critical value, then there is a significant correlation between the values. In other words, the correlation result is significant.

  2. Reply Avatar photo
    Rubayed Razib Suprov Aug 14, 2022 at 11:44 AM

    Thanks for your question in our platform.
    You may opt for the first method mentioned here to prevent Excel sheet content to another workbook completely.
    Or you can read the article( to prevent the workbook content from being copied to another workbook. You can ignore the VBA coding method if you want.

  3. Reply Avatar photo
    Rubayed Razib Suprov Aug 14, 2022 at 1:13 PM

    Use the following formula =IFERROR(VLOOKUP(B5,D5:E9,2,FALSE),””)
    Your given sample table is stored in column B: D. And this VLOOKUP function will look for the value of cell B5(table 1) in the first column on table 2, and then using that position, will return the meter reading from the E column. And it will return blank if there isn’t any meter reading.

  4. Reply Avatar photo
    Rubayed Razib Suprov Oct 2, 2022 at 12:41 PM

    The RAND function is supposed to return only a random value, which is used for shuffling the dataset values. In this case, we are trying to swap cell values within two separate cells. This means your statement is not applicable. If you insist that it is possible, please write down the process or send us your Excel file through email. Thank you.

  5. Reply Avatar photo
    Rubayed Razib Suprov Jan 29, 2023 at 10:57 AM

    Greetings TRISH,

    In your case, it is difficult to ans this problem without having a look at your worksheet . Please send the worksheet to our problem solving, hence we can assist you on this issue.

  6. Reply Avatar photo
    Rubayed Razib Suprov Jan 26, 2023 at 4:47 PM

    Greetings Debbie,

    First of all,we really appreciate for your question. Now if I am not afraid, you asked about why your total percentage value not changed despite deleting one sample value. If this is the case, the reason is pretty simple. as you delete one value, your total value also change. Which in turns also change the percentage value of all the other values. and this change will happen in a way that the total percentage values are always sums up to 100, following the basic arithmatic rule.
    If this is not the case,please provide your Excel worksheet containing your data and we will have a look.

    Thanks and Regards
    Rubayed Razib

  7. Reply Avatar photo
    Rubayed Razib Suprov Jan 26, 2023 at 1:58 PM

    Greetings Rob,
    Thanks a lot for commenting on this article. Unfortunately, we managed to find only the last three companies and the INVESTEC CORE FUND IWCF among the companies you metioned in the database. Rest of them are not enlisted in the database. I have given the dataset link containing the stock informations of the Satrix RESI ETF SATRIXRESI, Coronation Global Capital Plus [ZAR] Feeder CPLSZ, Ninety One Equity Fund NINEQ, INVESTEC CORE FUND IWCF etc.

    Thanks and Regards
    Rubayed Razib

    Dataset Link:

  8. Reply Avatar photo
    Rubayed Razib Suprov Dec 26, 2022 at 2:39 PM

    Thanks a lot for providing the correction. Both the Image and the Formula has been updated.

  9. Reply Avatar photo
    Rubayed Razib Suprov Dec 26, 2022 at 1:36 PM

    Hello Amghar,
    Thanks a lot for submitting Excel related problem at Exceldemy. Here you can use the VBA code mentioned below.

    Sub Detect_font_color()
    If Worksheets("Sheet1").Range("D6").Font.ColorIndex = 6 Then
    Range("D6").RowHeight = 20
    End If
    End Sub

    Detect Font Color and set row height to 40
    In that code, you need to mention the cell address inside the VBA code range property(as shown in the image). After this, if the condition satisfied, the cell row height will adjust accordingly.

  10. Reply Avatar photo
    Rubayed Razib Suprov Dec 26, 2022 at 1:10 PM

    Thanks a lot for submitting question in Exceldemy. From our end, every single method is actually working perfectly. Could you please specify which method you are experiencing this error with? Did you test with the dataset provided here? If not, could you please send us the Excel files?

  11. Reply Avatar photo
    Rubayed Razib Suprov Sep 4, 2022 at 1:03 PM

    Thanks a lot for your compliments, Raul. Glad to hear that the article came in handy for you

  12. Reply Avatar photo
    Rubayed Razib Suprov Aug 30, 2022 at 10:27 AM

    Thanks a lot for your comment, we will take your suggestions into consideration in the future.

  13. Reply Avatar photo
    Rubayed Razib Suprov Aug 30, 2022 at 10:22 AM

    Thanks for your question. Here Seattle has a rating of -0.50409…And St Louis Rams has -4.439784. if you subtract -0.50409 from the -4.439784, then you will get 4.943874.which is approximately around 4. So the statement that “Seattle has 4 points better than St Louis”

  14. Reply Avatar photo
    Rubayed Razib Suprov Aug 30, 2022 at 10:05 AM

    Thanks a lot for your appreciation.

  15. Reply Avatar photo
    Rubayed Razib Suprov Aug 30, 2022 at 10:04 AM

    Thanks a lot for your question. In the screenshot, all of the P values are actually below 0.05. One value is 0.002384973 and another one is 1.4769*10^-10. Maybe you got a little confused as the letter E is hard to notice. Hope this helps.

  16. Reply Avatar photo
    Rubayed Razib Suprov Aug 21, 2022 at 1:10 PM

    Thanks for your question, Jorge F. The allocation of memory mostly depends upon the dataset in which you are working on, and how they are interacting to other functions. For example, if you use a nested function, that function will cost more memory. There are some functions out there that consume more memory compared to other functions. Among the methods described above, the SUMPRODUCT function has more memory allocation compared to other functions. The other two methods would consume more or less the same amount of memory.

  17. Reply Avatar photo
    Rubayed Razib Suprov Aug 21, 2022 at 11:49 AM

    Thanks a lot for your question, Aaron. Here we are going to use a separate formula for highlighting the entire row based on whether any of the values in the row are blank or not. Notice the image below, we got blank cell in C8,C11, and C14

    2. First, go to the Conditional Formatting, and set the rules as shown below.

    3. Then select only the B4:D4, and copy the formatting style of this cell.

    4. Paste the formatting style all over the range of B4:D15.

    And now you can see that the rows now highlighted if there is any blank cell in the entire row.

  18. Reply Avatar photo
    Rubayed Razib Suprov Aug 21, 2022 at 11:10 AM

    Thanks a lot for your question. Here, if all of the cells on a single row are blank in your dataset, they will be highlighted if you follow this tutorial. If you have any further issues, feel free to inform us again.

  19. Reply Avatar photo
    Rubayed Razib Suprov Aug 14, 2022 at 1:38 PM

    Thanks for your question James, I am not quite sure in which step you get the first error. You can better opt for an alternative approach.
    1. The procedure above should be followed until the first extension change. After the extension is changed to .zip, use the 7-zip application to open the archive, not to extract the zip.

    2. Then inside the archive, go to xl>worksheet. and double click on the sheet1.xml to edit the text as mentioned in the post.

    3. you can use a regular notepad application for opening the XML file. Or you can also opt for an XML notepad( instead of a regular notepad. using the XML notepad, you can delete the protection part directly as a folder, as shown in the image.

    4. After deleting the protection part, save the XML file.
    5. And then change the extension part back to xls.
    6. You will see that the Excel file is now unprotected.
    Last but not the least, try to use Microsoft Office 365 instead of regular version of Excel.That way you will always be ensured with the latest updates.