# Changing Slope Calculation

1. ## Changing Slope Calculation

Hello Excel Enthusiasts!

I need a function that lets the user calculate the slope of something in another sheet, and they can type in the restraints on the range of that calculated slope. The sheet attached is self explanatory! I need 12 slopes to be calculated, but if I could just get the first one it is simple from there. I tried several things, but obviously to no avail!

Any help is appreciated!

2. ## Re: 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

3. ## Re: Changing Slope Calculation

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

4. ## Re: Changing Slope Calculation

See question below!

5. ## Re: Changing Slope Calculation

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?

7. ## Re: Changing Slope Calculation

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

8. ## Re: Changing Slope Calculation

Okay I went back to what I tried for the test sheet that I uploaded and my formula went something like this...

=SLOPE(ADDRESS(MATCH('Calculation Sheet'!\$B\$5,'Data to be referenced in sheet'!C:C,1),3,1,1,"'Data to be referenced in sheet'!"):ADDRESS(MATCH('Calculation Sheet'!\$C\$5,'Data to be referenced in sheet'!C:C,1),3,1,1,"'Data to be referenced in sheet'!"),ADDRESS(MATCH('Calculation Sheet'!\$B\$5,'Data to be referenced in sheet'!C:C,1),4,1,1,"'Data to be referenced in sheet'!"):ADDRESS(MATCH('Calculation Sheet'!\$C\$5,'Data to be referenced in sheet'!C:C,1),4,1,1,"'Data to be referenced in sheet'!"))

9. ## Re: Changing Slope Calculation

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.

10. ## Re: Changing Slope Calculation

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.

11. ## Re: Changing Slope Calculation

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.

12. ## Re: Changing Slope Calculation

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!

There are currently 1 users browsing this thread. (0 members and 1 guests)