+ Reply to Thread
Results 1 to 12 of 12

Find which column's numbers are most consistently falling

  1. #1
    Forum Contributor
    Join Date
    05-18-2011
    Location
    Delhi , India
    MS-Off Ver
    Excel 2007
    Posts
    171

    Find which column's numbers are most consistently falling

    Below is a data of 4 columns whose value is falling. I want to know which column's numbers are falling in a more consistent manner. For eg column A is most consistently falling whereas column C & D are stagnant. Can I translate it into a formula?
    Please Login or Register  to view this content.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Find which column's numbers are most consistently falling

    "Consistent". What does that mean?

    Maybe, prove it's falling using slope:
    =SLOPE(A1:A21,ROW(A1:A21))

    and then measure r-squared?
    =CORREL(A1:A21,ROW(A1:A21))^2

    But not knowing exactly what you are doing, it's hard to say!!
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Contributor
    Join Date
    05-18-2011
    Location
    Delhi , India
    MS-Off Ver
    Excel 2007
    Posts
    171

    Re: Find which column's numbers are most consistently falling

    Quote Originally Posted by Glenn Kennedy View Post
    "Consistent". What does that mean?

    Maybe, prove it's falling using slope:
    =SLOPE(A1:A21,ROW(A1:A21))

    and then measure r-squared?
    =CORREL(A1:A21,ROW(A1:A21))^2

    But not knowing exactly what you are doing, it's hard to say!!
    Consistent would mean the "frequency" in falling should be high. The formula should reflect high importance to an uptick. For example below column is very similar to Column A. But in between (15&16) there is a spike up. With the slope formula the values are coming very similar.
    Please Login or Register  to view this content.

  4. #4
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Find which column's numbers are most consistently falling

    I'm bad in mathematics so I've to guess. >_<

    may be..

    Please Login or Register  to view this content.
    While G2:G22 is range of data.

    Regards.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Find which column's numbers are most consistently falling

    Slope is irrelevant, only insofar as it should be negative, to prove it's falling, overall. You need to know/anticipate/chart HOW the numbers are falling. The R2 I used was for a simple linear regression. Without knowing what sort of decline you are EXPECTING to see (linear, exponential, etc, ) it's hard to be more precise.

    Are these numbers biological measurments of something... or what?

  6. #6
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Find which column's numbers are most consistently falling

    Or else (by using helper columns) , I's hard for me to explain, just can say that
    [ using decline % to indicate differents of each elements ]
    result is in yellow cell , which is nearest 0 is the best. (maybe)


    Regards.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    05-18-2011
    Location
    Delhi , India
    MS-Off Ver
    Excel 2007
    Posts
    171

    Re: Find which column's numbers are most consistently falling

    Quote Originally Posted by Glenn Kennedy View Post
    Slope is irrelevant, only insofar as it should be negative, to prove it's falling, overall. You need to know/anticipate/chart HOW the numbers are falling. The R2 I used was for a simple linear regression. Without knowing what sort of decline you are EXPECTING to see (linear, exponential, etc, ) it's hard to be more precise.

    Are these numbers biological measurments of something... or what?
    The data is variance of a stock price in a period of time. I need to know that the variance should be falling and not be stagnant or increasing. Was wondering how to represent it in a formula and derive a mathematical value.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Find which column's numbers are most consistently falling

    Is the x-axis linear? Hours, days, minutes??

  9. #9
    Forum Contributor
    Join Date
    05-18-2011
    Location
    Delhi , India
    MS-Off Ver
    Excel 2007
    Posts
    171

    Re: Find which column's numbers are most consistently falling

    Quote Originally Posted by Glenn Kennedy View Post
    Is the x-axis linear? Hours, days, minutes??
    Minutes...

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Find which column's numbers are most consistently falling

    It sounds a bit subjective!! So I plotted them and the data in A looked like an exponential decline. So I fitted them all with an exponential equation. Clearly dataset A is declining and it has a very high R2, indicating that it is a very good fit to the exponential formual. If you hadn't told me thta the expected answer was A, I'd have reached that conclusuion myself. Th elower the value of R2, the grater the deviation of th epopulation from an exponential fit. It's always worth plotting data, to see what they look like...
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    05-18-2011
    Location
    Delhi , India
    MS-Off Ver
    Excel 2007
    Posts
    171

    Re: Find which column's numbers are most consistently falling

    Quote Originally Posted by Glenn Kennedy View Post
    It sounds a bit subjective!! So I plotted them and the data in A looked like an exponential decline. So I fitted them all with an exponential equation. Clearly dataset A is declining and it has a very high R2, indicating that it is a very good fit to the exponential formual. If you hadn't told me thta the expected answer was A, I'd have reached that conclusuion myself. Th elower the value of R2, the grater the deviation of th epopulation from an exponential fit. It's always worth plotting data, to see what they look like...
    Yes it is a bit subjective but this looks good. Column A is very easy to conclude. The problem comes with identifying Column C&D. Can you tell me the formula for R2 as i would not be able to plot the data in a graph?

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Find which column's numbers are most consistently falling


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Find the cell at which it becomes consistently above a 'set value'
    By miniphillips in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-13-2018, 06:15 AM
  2. Replies: 4
    Last Post: 10-19-2017, 08:12 AM
  3. Replies: 17
    Last Post: 03-26-2014, 12:57 AM
  4. [SOLVED] Re: Find lowest 5 numbers in column A with highest values in column B
    By jd16 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-05-2013, 02:08 AM
  5. Replies: 4
    Last Post: 10-06-2013, 10:40 AM
  6. Find lowest 5 numbers in column A with highest values in column B
    By dmccoy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-06-2013, 09:22 AM
  7. Search for column header and find all values and their row numbers in that column
    By woody83 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-14-2012, 03:11 PM

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