Excel won't sort my values properly

[KoshX87]

So basically I have an assignment where I need to get stock values for 2 different stocks. The next part is where I had to get the log returns for each of the stocks. So the formula was a recursive one where R(t) = ln(S(t)) - ln(S(t-1)), where R(t) is the return for the stock at time t and S(t) is the Stock price at time t. So i've done this and now i have 1260ish values for R(t) over 5 years. All i want to do with these values is sort them from the lowest to the highest. I highlight the entire colume of R(t) which includes negative and positive values between -1 and 1. After highlighting I click on the sort from smallest to largest but it doesn't work properly. I'm not sure if its because the values in these columns are achieved through formulas or not.

This is the excel file: Assignment Excel File.xlsx.

If you could go to the 3rd sheet labelled "Normality Test" and look at those 2 columns and tell me why it won't sort the second column (column J in the sheet) from the smallest negative to the greatest positive. If I was to type the numbers -3 -10, 5, -7, 2, 3 and then used the sort function it would give me: -10, -7, -3, 2, 3, 5, which is what I want, but i think since these cells have formula dependent on other cells it won't work. It'd be nice if there's a way around this because i need to sort the values to do the normality test.

Thanks =]

[MarvinP]

Hi Kosh and welcome to the forum,

The formula in J5 says to look at H5 and subtract H4. This formula is relative to what cell it is in. Now - you are trying to sort column J. It won't sort because each cell is built based on the values in column H.

If you were to copy column J and then Paste it using "Values Only" it would sort, as then it wouldn't be formulas any more. See if that is what you need.

[KoshX87]

Thank you Marvin. That's exactly what I needed. I did feel as though the formulae were causing some problems. The sort works now.

Thanks.