+ Reply to Thread
Results 1 to 12 of 12

Changing Slope Calculation

  1. #1
    Registered User
    Join Date
    06-29-2011
    Location
    South Carolina
    MS-Off Ver
    Excel 2003
    Posts
    20

    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!
    Attached Files Attached Files
    Last edited by alecp; 02-09-2012 at 10:32 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,141

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

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,769

    Re: Changing Slope Calculation

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

  4. #4
    Registered User
    Join Date
    06-29-2011
    Location
    South Carolina
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Changing Slope Calculation

    See question below!
    Last edited by alecp; 02-08-2012 at 01:59 PM. Reason: Further assistance needed

  5. #5
    Registered User
    Join Date
    06-29-2011
    Location
    South Carolina
    MS-Off Ver
    Excel 2003
    Posts
    20

    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?

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,769

    Re: Changing Slope Calculation

    How about the SLOPE() function?

  7. #7
    Registered User
    Join Date
    06-29-2011
    Location
    South Carolina
    MS-Off Ver
    Excel 2003
    Posts
    20

    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


    I hope that made sense...

  8. #8
    Registered User
    Join Date
    06-29-2011
    Location
    South Carolina
    MS-Off Ver
    Excel 2003
    Posts
    20

    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'!"))


    I guess you can't do slope(address():address(),address():address()). Idk I'm relatively new to this whole reference function game
    Last edited by alecp; 02-08-2012 at 04:50 PM. Reason: oops! reference off

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,769

    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. #10
    Forum Contributor
    Join Date
    02-08-2012
    Location
    South Suffolk
    MS-Off Ver
    Excel 2007
    Posts
    102

    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. #11
    Forum Contributor
    Join Date
    02-08-2012
    Location
    South Suffolk
    MS-Off Ver
    Excel 2007
    Posts
    102

    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.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    06-29-2011
    Location
    South Carolina
    MS-Off Ver
    Excel 2003
    Posts
    20

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1