+ Reply to Thread
Results 1 to 5 of 5

how to get max of a range x rows above a reference row

  1. #1
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    how to get max of a range x rows above a reference row

    I've attached a worksheet which illustrates a desired answer for a formula I don't know how to write.

    The challenge for me is this: As an example, say that column b has a range of numbers. elsewhere in the sheet I've set a variable, which in this case is 12.

    in column C I want to get the max of a range which begins 12 rows up in column B, but goes down 1/2 the variable of 12.

    any advice? thanks.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: how to get max of a range x rows above a reference row

    Try this...
    =MAX(OFFSET($B$1,COUNT($B$1:$B$15)-$F$1,,$F$1/2,1))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: how to get max of a range x rows above a reference row

    OR
    C13=MAX(OFFSET(B13,-MIN(F1,ROWS(B$1:B13))+1,,INT(F1/2)))
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: how to get max of a range x rows above a reference row

    Thanks. nflsales formula worked for me, possibly because the # of rows in my actual workbook varies, up to 2,000.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: how to get max of a range x rows above a reference row

    As long as you got what you needed

    Thanks for the feedback!

+ 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] Index-Match, Reference Range Jumps 8 Rows if Dragged Down
    By uckycool in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-05-2018, 10:04 PM
  2. Reference expanding range to autopopulate multiple rows
    By swilliams658 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-13-2017, 01:27 AM
  3. OFFSETTING a reference range to exclude 3 bottom rows
    By drgkt in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-30-2017, 06:10 PM
  4. [SOLVED] VBA to reference sheet range and populate with spacing between rows
    By mrjackg in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-11-2017, 04:56 PM
  5. Replies: 2
    Last Post: 12-14-2015, 06:02 AM
  6. [SOLVED] Locked formula changes reference when rows added to reference sheet in same workbook
    By macrorookie in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-19-2014, 04:08 PM
  7. Replies: 1
    Last Post: 11-11-2009, 01:08 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