User Posts: Rubayed Razib Suprov
How to Create a Thermometer Chart in Excel (with Easy Steps)

Thermometer-type looking and functioning charts can bring your Excel Charts to life. This Chart is visually eye-pleasing and has better clarity. If you are ...

Excel Conditional Formatting Icon Sets Based on Percentage

When working with big amounts of data, it is often important to format the data in Excel in specific ways to improve readability. If you are curious to know ...

Two Way ANOVA in Excel with Unequal Sample Size (2 Examples)

The full form of the ANOVA is Analysis of Variance. This test helps us to identify whether there is a similarity in values between two data samples. or whether ...

How to Repeat Header Row When Scrolling in Excel (6 Ways)

Sometimes, while working with a big dataset, we need to Repeat rows in Excel when scrolling. This feature helps to keep important rows visible during the time ...

Calculating Payback Period in Excel with Uneven Cash Flows

For any business undertaking any kind of decision, especially whether to make an investment in a new venture or reinvest in an old project, calculating the ...

How to Create a Schema in Excel (with Detailed Steps)

The Schema-based XML files can help us map the entire worksheet and create a new XML file in a short period of time. If you are curious to know how you can ...

How to Calculate Incremental Cash Flow in Excel (2 Examples)

For any business undertaking any kind of decision, especially whether to make an investment in a new venture or reinvest in an old project, Incremental Cash ...

Excel VBA: Show Userform in Full Screen (4 Easy Ways)

Often the Userform we generate is quite big and contains a lot of information in a small place. Having all of them in a crammed place can hamper the clarity of ...

[Fixed!] Why Are Some of My Gridlines Not Showing in Excel?

Gridline helps us have a clear idea of cell numbering, in other words, cell address. But it will be very annoying if some of that grid line is missing from the ...

How to Unhide All Columns with Excel VBA (8 Examples)

For various reasons, we hide columns and rows in Excel. At the same time, we may need to unhide them for our needs. Unhiding them may involve various types of ...

How to Calculate WACC in Excel (with Easy Steps)

  WACC is a useful parameter that can help you to extract insight about a company. You can use this parameter to decide whether you should invest or ...

How Do I Stop 255 Character Limit in Excel (4 Ways)

During the many calculations phase, we encounter that text in cell formulas needs to be under 255 characters, which is annoying. In most cases, we have no ...

How to Leave Cell Blank If There Is No Data in Excel (5 Ways)

During the many calculations phase, we encounter Blank cells, which leads to an output of zero. But many cases, the Blank cell compared to the zero value as ...

[Fixed!] Print Preview in Excel Doesn’t Match Document

It is quite irritating for anyone if they discover that the Print Preview of their document does not match the Printed edition. If you are curious as to how ...

Dealing with Tables with Changing Headers in Power Query

As in real time it happens a lot, we tend to update our original data source a lot, during that period, we may alter the column Header a bit inside the Power ...

Browsing All Comments By: Rubayed Razib Suprov
  1. Reply
    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
    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
    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
    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
    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

  6. Reply
    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.

  7. Reply
    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”

  8. Reply
    Rubayed Razib Suprov Aug 30, 2022 at 10:05 AM

    Thanks a lot for your appreciation.

  9. Reply
    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.

  10. Reply
    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.

  11. Reply
    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.

  12. Reply
    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.

  13. Reply
    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.