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)

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)

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, [email protected] 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)

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)

On Saturday, 29 August 2020 at 07:02:00 UTC-6, [email protected] 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)