+ Reply to Thread
Results 1 to 13 of 13

Excel 2007 : Help with formula to find previous value in column

  1. #1
    Registered User
    Join Date
    06-04-2012
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2007
    Posts
    6

    Help with formula to find previous value in column

    Need help on a formula that will return the next lesser value in column F that is less than the value of row 17 (45.81) (i.e. 44.81). The formula needs to look back in column F in cells above the current value to find the next value that is less than and skip the blank cells. Can anyone help, this one has me stumped.

    ScreenHunter_15 Jun. 04 13.21.jpg

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with formula to find previous value in column

    Maybe like this.

    Please post an excel example in the future.
    Attached Files Attached Files
    Last edited by oeldere; 06-04-2012 at 01:43 PM.

  3. #3
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Help with formula to find previous value in column

    Hi Kdub,

    Welcome to the forum.

    Please upload a sample file instead of image as it saves time of creating a similar workbook for practical try.. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  4. #4
    Registered User
    Join Date
    06-04-2012
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Help with formula to find previous value in column

    Sample file attached
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with formula to find previous value in column

    And did you try my solution?

  6. #6
    Registered User
    Join Date
    06-04-2012
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Help with formula to find previous value in column

    Doesn't seem to work...for instance I would need the value in G103 to return 129.99 because it is the most recent value less than 131.17. The formula needs to continue down the column so that anytime a new value is entered the subsequent most recent lesser value is returned.

  7. #7
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Help with formula to find previous value in column

    Hi Kdub,


    See the yellow cells in attached file.... thanks.


    Sample File.xls
    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  8. #8
    Registered User
    Join Date
    06-04-2012
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Help with formula to find previous value in column

    Thanks. That seems to work to some degree, however G103 should return 129.99 and , G100 is corrent and G97 should return 127.72 (the value from E10) which would have been the most recent value less than the current value.

  9. #9
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Help with formula to find previous value in column

    Okay... that way...
    I understood this and revised the formula in the attached file.
    Sample File.xls

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  10. #10
    Registered User
    Join Date
    06-04-2012
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Help with formula to find previous value in column

    Thanks, that worked. Now, similarly I'm trying to find the higher value (instead of lesser value) in column H but when I reverse the formula it doesn't show like the values do in column G. I would like to see the values in column I read like G instead with the most recent previous higher value. I have plugged in the formula but can't seem to get it to work.
    Attached Files Attached Files

  11. #11
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Help with formula to find previous value in column

    You are welcome kdub..

    Earlier you required
    "most recent value less than the current value"
    , now what do you need.. I am not clear with you post #10.
    Do you need most recent value higher than the current value?

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  12. #12
    Registered User
    Join Date
    06-04-2012
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Help with formula to find previous value in column

    That's correct - when I try revising the formula to find the most recent previous value that was higher than the current value, it seems to return some inconsistent values in column I. Specifically the "DTL" is showing as opposed to the numerical values as seen in col G.
    Last edited by kdub; 06-05-2012 at 12:42 PM.

  13. #13
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Help with formula to find previous value in column

    Hi Kdub,

    Cell I103 has 136.87 which is the most recent value higher than current value (133.93).. so it is correct.
    Else, share you thoughts alongwith data you are expecting. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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