Thread: Changing Slope Calculation

Formulae added in attached workbook, along with some notes. You can copy row 5 down further for other values.

Hope this helps.

Pete

How about this:

What I did was use a simple VLOOKUP to look up the value for each day entered. From there the average slope is simply Delta f/Delta t or (f2-f1)/(t2-t1).

Ah I think I should have maybe phrased the question differently.

Due to the high amount of variance in my data set, I need for the slopes to be calculated from the entire range of data - essentially I want to see the same values as if you were going to to a linear fit line and read off the x coefficient from that. Otherwise if the particular day is extremely high or low, it might give erroneous results. Is there anyway to make that calculation in the way I am asking?

How about the SLOPE() function?

I tried it, but I couldnt get the ranges to reference correctly with the other sheet and idk why. I tried a function but I accidentally deleted it...it had a =slope(address(match(time1)):address(match(time2))) type thing in there, with obviously more stuff in each of the functions...but I guess what I don't understand is how to use the address function with the array that slope wants...i.e. =slope(E4:E50,D4:D50) would work...but does =slope(address():address(), address():address()) work? That may be a newbie way to do it, but I was hoping it would work...maybe I just typed it in incorrectly. I'll build it back to what I had in the meantime


I hope that made sense...

I would suggest you look at the INDIRECT function. I never use it, so I'm not very good at using it, but you basically build your cell reference as a text string, then you can use the INDIRECT function inside of the SLOPE function to get the result.

Another way is to use OFFSET to define each of the ranges needed in the SLOPE function. OFFSET allows you to define a range based on a starting position and a number of rows. MATCH could be used within OFFSET() to get the correct position.

I am a bit short of time now but if potential solution appeals, I can work up the formula tomorrow.

I have now put the formula together in the attached sheet. To help explain it, one version shows the build-up of the constituent parts using the MATCH function to identify the start row and the number of rows to use. The main work is done using OFFSET to specify the applicable ranges. OFFSET() is quite a tricky but powerful function - usually playing with a few examples helps understanding.

I am an advocate of using range names where possible and have put in a couple to help readability in the formulae.

Awesome! Thanks to all of you who helped, also I really liked the formula breakdown of the offset function. That helped visualize it.

Have a nice day!