+ Reply to Thread
Results 1 to 11 of 11

MINMAX via Offset from last value in column

  1. #1
    Forum Contributor
    Join Date
    08-17-2013
    Location
    Asia
    MS-Off Ver
    Excel 2010
    Posts
    271

    MINMAX via Offset from last value in column

    Find Min or Max [for last n rows] value from single column where row data keeps expanding

    Friends / Seniors,

    My current data starts from Cell = C12. The last data is in Cell C31 , and this data would keep incerasing from C31 to C32 to C33 and so on. Starting from Bottom Last Cell in Column C having value i.e. C31, I want to [in Cell C7] find previous n th data (controller in Cell A7), currently for last 10 rows from bottom C31).
    Presently, C7 is =OFFSET($C$31,-A7,0).

    Starting from Last cell having values, to move upward steps based in A7 value. So, defined Range is C21:C30.

    Assuming max data size could be upto C3000, I tried to get address of last cell in Column C using array formula in Cell C9 =ADDRESS(MAX((C12:C3000<>"")*(ROW(C12:C3000))),COLUMN(C12:C3000))

    Now, how do i get MIN or MAX value from these range C21:C30. Min value result 17.64[C23], and Max 19.90[C27]. Issue is to how to make use of MIN/MAX function in given situation, and how do i get Cell reference to offset from the last cell.

    How to get the result in C7?
    Sample file attached.
    Attached Files Attached Files

  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: MINMAX via Offset from last value in column

    I would NOT use OFFSET for a start. Try this:

    =INDEX(C:C,MATCH(1E+100,Sheet1!$C:$C)-A7,)
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 12-17-2020 at 05:35 AM.
    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 Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: MINMAX via Offset from last value in column

    Please try
    for C21:C30
    =MIN(INDEX(C12:C999,MATCH(9^9,C12:C3000)-1):INDEX(C12:C999,MATCH(9^9,C12:C3000)-A7))
    =MAX(INDEX(C12:C999,MATCH(9^9,C12:C3000)-1):INDEX(C12:C999,MATCH(9^9,C12:C3000)-A7))

    or for C21:C31
    =MIN(INDEX(C12:C999,MATCH(9^9,C12:C3000)):INDEX(C12:C999,MATCH(9^9,C12:C3000)-A7))
    =MAX(INDEX(C12:C999,MATCH(9^9,C12:C3000)):INDEX(C12:C999,MATCH(9^9,C12:C3000)-A7))
    Attached Files Attached Files

  4. #4
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: MINMAX via Offset from last value in column

    Try with
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  5. #5
    Forum Contributor
    Join Date
    08-17-2013
    Location
    Asia
    MS-Off Ver
    Excel 2010
    Posts
    271

    Re: MINMAX via Offset from last value in column

    Many thanks for all feedback, but how to find MAX or MIN within a range considering A7 value?

    What really i wanted is to consider nth value from last cell in Column C and take MIN or MAX value within that nth Range.
    Last edited by analystbank; 12-17-2020 at 12:28 PM.

  6. #6
    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: MINMAX via Offset from last value in column

    Oops. Let's try to post my reply again. I did something daft first time, a few moments ago!!

    This will give min & max of last n rows where A7 defines n.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    08-17-2013
    Location
    Asia
    MS-Off Ver
    Excel 2010
    Posts
    271

    Re: MINMAX via Offset from last value in column

    Quote Originally Posted by Glenn Kennedy View Post
    Oops. Let's try to post my reply again. I did something daft first time, a few moments ago!!

    This will give min & max of last n rows where A7 defines n.
    Many Thanks and hats off to you and all others. Exactly, what i needed. Infact, in my #1, where i say

    Assuming max data size could be upto C3000, I tried to get address of last cell in Column C using array formula in Cell C9 =ADDRESS(MAX((C12:C3000<>"")*(ROW(C12:C3000))),COLUMN(C12:C3000))
    was infact, from one of the thread you had given this formula.

    Few follow up questions on given solutions, condiering I would have other Data and calculations in C1:C6 range

    1) as you bring in data in cell C7 to know the start of Range , would it affect result in above range ?
    2) Result in C7 calls for start of the range (from Control in A7), does it find the cell reference or value, why i am asking is because, if it refers to cell value, then, perhaps, in case of duplicate data in growing data range C7 till bottom, could jeoparadise.
    Last edited by analystbank; 12-18-2020 at 03:54 AM.

  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: MINMAX via Offset from last value in column

    Ignore what is in C7, it can be deleted. It is left over from when I did not really understand what you wanted.

    I'm sorry, but I do not understand what you mean by

    "Result in C7 calls for start of the range (from Control in A7), does it find the cell reference or value, why i am asking is because, if it refers to cell value, then, perhaps, in case of duplicate data in growing data range C7 till bottom, could jeoparadise."

  9. #9
    Forum Contributor
    Join Date
    08-17-2013
    Location
    Asia
    MS-Off Ver
    Excel 2010
    Posts
    271

    Re: MINMAX via Offset from last value in column

    Ok, Sir. I am just tweaking with other range data and will revert. I am closing this thread here, if need be, would buzz you with this thread. Many thanks

  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: MINMAX via Offset from last value in column

    OK. Shout if you have problems. You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

  11. #11
    Forum Contributor
    Join Date
    08-17-2013
    Location
    Asia
    MS-Off Ver
    Excel 2010
    Posts
    271

    Re: MINMAX via Offset from last value in column

    I have already added reputation, Sir. Gratitude, always. Many thanks for wonderful support.

+ 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] If last cell in column A is Greater than column B Then Offset Column A
    By Ratso in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-11-2018, 11:40 AM
  2. Replies: 5
    Last Post: 10-23-2015, 12:35 PM
  3. Replies: 3
    Last Post: 06-01-2014, 10:26 AM
  4. MinMax formula problem
    By bqheng in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-08-2014, 08:14 PM
  5. Matching a value to a column, (rounding) and returning offset column value
    By jayinthe813 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-19-2013, 02:42 PM
  6. Search Sheet2 Column A For Value In Textbox On Sheet1 Then Offset Right By 1-5 Column
    By sweetrevelation in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-08-2009, 01:32 PM
  7. Counting cells in offset column based on contents of another column.
    By Big Chris in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-10-2008, 09:25 AM

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