User Posts: Rubayed Razib Suprov
How to Create a Schedule in Excel That Updates Automatically

Creating a schedule is a part and parcel of many of our lives. Creating the schedule automatically will be immensely helpful for many people. If you are ...

How to Swap Cells in Excel (3 Easy Methods)

While working in Excel, we might need to re-arrange data between rows and cells. Sometimes there is a possibility when you need to swap your data cells or ...

How to Vlookup and Return Multiple Values in Drop Down List

VLOOKUP is a useful function to look for the values in the dataset and return the necessary part of that dataset. If you are curious to know how you can use ...

SUMIFS with INDEX-MATCH Formula Including Multiple Criteria

Summation is a kind of part and parcel that Excel uses. SUMIFS function made this process more dynamic by using many criteria. If you are curious to know how ...

How to Change Color of Toggle Button When Pressed in Excel

When you have a toggle button in your worksheet, you can use it to program certain things in your workbook. But at the same time, if the color of the toggle ...

How to Check Spelling and Grammar in Excel (4 Ways)

We are using Excel sheets with people from a wide range of backgrounds. It will be pretty annoying and can create an embarrassing situation if the worksheet ...

How to Highlight Cell If Value Is Less Than Another Cell in Excel

In many instances, the users might be in argent need of highlighting the cell values that are less than or greater than some other values stored in another ...

How to Create an Employee Timesheet in Excel (with Easy Steps)

Do you urgently require an Employee Timesheet? You've found the right place if the response is yes. Excel makes it very easy to manage and track work hours. In ...

How to Open Workbook as Read-Only with Excel VBA

Naturally, we may need to open the file from a parent Excel file using VBA as a read-only form. For the reason of keeping the original file intact. If you are ...

[Fixed!] Excel Links Not Updating Unless Source Is Open

In Microsoft Excel, working with multiple workbooks is a common task. You may have connections or links between these workbooks. It helps to visualize the ...

Return Expected Value If Time Is Greater Than 1 Hour in Excel

Naturally, time is important for us, and so does whether we lag behind it or not. It is pretty normal to wonder if are going to lag behind from a certain ...

How to Break Links in Excel When Source Is Not Found (4 Ways)

In Microsoft Excel, working with multiple workbooks is a common task. You may have connections or links between these workbooks. It helps to visualize the ...

How to Merge Two Tables in Excel (5 Methods)

In the workspace, we frequently encounter the need of merging two separate tables with each other. Merging two separate tables will increase the clarity of the ...

How to Show Grand Total with Secondary Axis in Pivot Chart

While creating a Pivot Chart in Excel, showing the Grand Total can help understand the chart and increase clarity. If you are curious to know how you can open ...

How to Move Every Other Row to Column in Excel (6 Ways)

Sometimes we need to move every odd or even row to other cells in the worksheets. Obviously, it can be done manually. But if the worksheet size is too big, ...

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 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

  5. 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.

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

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

    Thanks a lot for your appreciation.

  8. 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.

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

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

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

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