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.

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.

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.

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.

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:https://www.exceldemy.com/wp-content/uploads/2023/01/Get-Stock-Prices-in-Excel.xlsx

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

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.

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

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”

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.

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.

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(https://microsoft.github.io/XmlNotepad/##_top) 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.