• Determine Maximum value in a range define by a cell value

    From David Cuthill@21:1/5 to All on Fri Aug 28 15:21:11 2020
    I am trying to determine the maximum value in a subrange of a larger range with the starting row and ending row of the subrange determined from the values placed within a column cells. Cell 1 would determine the upper end of the range and Cell 2 the
    lower end. I then need to copy this formula downward in which case Cell 2 would be the upper reference and Cell 3 the lower reference - and so on.

    I cannot seem to be able to figure out a formula that captures this.

    thanks for any assistance

    David

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Sat Aug 29 02:01:05 2020
    Hi David,

    Am Fri, 28 Aug 2020 15:21:11 -0700 (PDT) schrieb David Cuthill:

    I am trying to determine the maximum value in a subrange of a larger range with the starting row and ending row of the subrange determined from the values placed within a column cells. Cell 1 would determine the upper end of the range and Cell 2 the
    lower end. I then need to copy this formula downward in which case Cell 2 would be the upper reference and Cell 3 the lower reference - and so on.

    your numbers from A1 downwards and the borders of the range from B1
    downwards, then try: =IF(B1>COUNT(A:A),"",MAX(INDIRECT("A"&B1&":A"&IF(B2<=COUNT(A:A),B2,COUNT(A:A)))))



    Regards
    Claus B.
    --
    Windows10
    Office 2016

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From David Cuthill@21:1/5 to claus...@t-online.de on Fri Aug 28 17:21:42 2020
    Hi Claus
    Thanks for your reply - your suggestion sort of does what I need.

    To clarify ... I have 2 columns of data. Column B defines what portion of Column A I need to analyze. So the first value in column B (2) indicates I want to determine the maximum value in A from the rows 1-2 and the next value in B (4) is indicating that
    I want to know the max value in A for rows 3-6 (the next 4 rows) and so on.

    I don't think I was very clear on my original post.

    A B
    10 2
    20 4
    11 4
    21 5
    31
    41
    12
    22
    32
    42
    13
    23
    33
    43
    53


    On Friday, 28 August 2020 at 18:01:09 UTC-6, claus...@t-online.de wrote:
    Hi David,
    Am Fri, 28 Aug 2020 15:21:11 -0700 (PDT) schrieb David Cuthill:

    I am trying to determine the maximum value in a subrange of a larger range with the starting row and ending row of the subrange determined from the values placed within a column cells. Cell 1 would determine the upper end of the range and Cell 2 the
    lower end. I then need to copy this formula downward in which case Cell 2 would be the upper reference and Cell 3 the lower reference - and so on.
    your numbers from A1 downwards and the borders of the range from B1 downwards, then try: =IF(B1>COUNT(A:A),"",MAX(INDIRECT("A"&B1&":A"&IF(B2<=COUNT(A:A),B2,COUNT(A:A)))))



    Regards
    Claus B.
    --
    Windows10
    Office 2016

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Sat Aug 29 15:01:54 2020
    Hi David,

    Am Fri, 28 Aug 2020 17:21:42 -0700 (PDT) schrieb David Cuthill:

    To clarify ... I have 2 columns of data. Column B defines what portion of Column A I need to analyze. So the first value in column B (2) indicates I want to determine the maximum value in A from the rows 1-2 and the next value in B (4) is indicating
    that I want to know the max value in A for rows 3-6 (the next 4 rows) and so on.

    I don't think I was very clear on my original post.

    A B
    10 2
    20 4
    11 4
    21 5
    31
    41
    12
    22
    32
    42
    13
    23
    33
    43
    53

    then you need 2 formulas.
    For the first region:
    =MAX(OFFSET($A$1,,,B1))
    For the other regions:
    =MAX(OFFSET($A$1,SUM(B$1:B1),,B2))
    and copy the second formula down.


    Regards
    Claus B.
    --
    Windows10
    Office 2016

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From David Cuthill@21:1/5 to claus...@t-online.de on Sat Aug 29 17:14:48 2020
    On Saturday, 29 August 2020 at 07:02:00 UTC-6, claus...@t-online.de wrote:
    Hi David,
    Am Fri, 28 Aug 2020 17:21:42 -0700 (PDT) schrieb David Cuthill:

    To clarify ... I have 2 columns of data. Column B defines what portion of Column A I need to analyze. So the first value in column B (2) indicates I want to determine the maximum value in A from the rows 1-2 and the next value in B (4) is indicating
    that I want to know the max value in A for rows 3-6 (the next 4 rows) and so on.

    I don't think I was very clear on my original post.

    A B
    10 2
    20 4
    11 4
    21 5
    31
    41
    12
    22
    32
    42
    13
    23
    33
    43
    53
    then you need 2 formulas.
    For the first region:
    =MAX(OFFSET($A$1,,,B1))
    For the other regions:
    =MAX(OFFSET($A$1,SUM(B$1:B1),,B2))
    and copy the second formula down.
    Regards
    Claus B.
    --
    Windows10
    Office 2016

    thank you that did the trick

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)