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 thelower 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.
Hi David,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.
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
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
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 indicatingthat 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
Hi David,that I want to know the max value in A for rows 3-6 (the next 4 rows) and so on.
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
I don't think I was very clear on my original post.
A Bthen you need 2 formulas.
10 2
20 4
11 4
21 5
31
41
12
22
32
42
13
23
33
43
53
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
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 285 |
Nodes: | 16 (2 / 14) |
Uptime: | 73:37:03 |
Calls: | 6,489 |
Calls today: | 2 |
Files: | 12,096 |
Messages: | 5,275,840 |