Max Difference Between Two Dynamic Ranges

[excelstreet]

Let's assume our example data set is as follows:

Column A1:A9
10
12
14
12
10
15
20
25
20

Column B1:B9
10
10
11
11
9
9
11
11
10


I'm looking for a one cell formula/vba code that will show the maximum difference between the maximum value of a dynamically increasing range in column A1:A9 and minimum value of a dynamically decreasing range in column B1:B9.

The calculation would ideally go as follows:

Max(A1:A1) - Min(B1:B9) = 10 - 9 = 1
Max(A1:A2) - Min(B2:B9) = 12 - 9 = 3
Max(A1:A3) - Min(B3:B9) = 14 - 9 = 5
Max(A1:A4) - Min(B4:B9) = 14 - 9 = 5
Max(A1:A5) - Min(B5:B9) = 14 - 9 = 5
Max(A1:A6) - Min(B6:B9) = 15 - 9 = 6
Max(A1:A7) - Min(B7:B9) = 20 - 10 = 10
Max(A1:A8) - Min(B8:B9) = 25 - 10 = 15
Max(A1:A9) - Min(B9:B9) = 25 - 10 = 15

Maximum Difference Between These Two Dynamic Ranges = 15

Here's my attempt so far

Function MaxDiff(High As Range, Low As Range) As Double

' Variable Declaration
Dim MaxRange As Range
Dim MaxValue As Double
Dim MinRange As Range
Dim MinValue As Double
Dim Max As Double

Max = 0

' Find Maximum Value
Do While i < High.Cells.Rows.Count + 1
i = i + 1
    MaxRange = Range(High.Cells(1, 1).Address, High.Cells(i, 1).Address)
    MaxValue = WorksheetFunction.Max(MaxRange)
    MinRange = Range(Low.Cells(i, 1).Address, Low.Cells(Low.Cells.Rows.Count, 1).Address)
    MinValue = WorksheetFunction.Min(MinRange)
        If MaxValue - MinValue > Max Then
            Max = MaxValue - MinValue
            End If
Loop

MaxDiff = Max

End Function

Any help would be greatly appreciated!

 

[excelstreet]

For context, I'm specifically looking for the dynamic ranges to compare the difference in this way because of the following possibility.

Let's say another data set is as follows:

A1:A4
17
19
20
18

B1:B4
12
13
13
13

Ideally the Result Would Be:

Max(A1:A1) - Min(B1:B4) = 17 - 12 = 5
Max(A1:A2) - Min(B2:B4) = 19 - 13 = 6
Max(A1:A3) - Min(B3:B4) = 20 - 13 = 7
Max(A1:A4) - Min(B4:B4) = 20 - 13 = 7

Maximum Difference Between These Two Dynamic Ranges = 7

However MAX(A1:A4) - MIN(B1:B4) = 8

Due to this, I'm trying to create a dynamically difference that would calculate in the way explained above.

 

[StephenCrump]

Welcome to the Forum!

A couple of tweaks to your code and it seems to work:

Function MaxDiff(High As Range, Low As Range) As Double

' Variable Declaration
Dim MaxRange As Range
Dim MaxValue As Double
Dim MinRange As Range
Dim MinValue As Double
Dim Max As Double

Max = 0

' Find Maximum Value
Do While i < High.Cells.Rows.Count + 1
    i = i + 1
    Set MaxRange = Range(High.Cells(1, 1), High.Cells(i, 1))
    MaxValue = WorksheetFunction.Max(MaxRange)
    Set MinRange = Range(Low.Cells(i, 1), Low.Cells(Low.Cells.Rows.Count, 1))
    MinValue = WorksheetFunction.Min(MinRange)
    If MaxValue - MinValue > Max Then
        Max = MaxValue - MinValue
    End If
Loop

MaxDiff = Max

End Function

You could also use a formula: =MAX(SUBTOTAL(4,OFFSET(A1:A9,,,ROW(A1:A9)-ROW(A1)+1))-SUBTOTAL(5,OFFSET(B9,ROW(B1:B9)-ROW(B9),,1+ROW(B9)-ROW(B1:B9))))

(You may need to array-enter this - I'm struggling to remember life before Excel 365)

 

[Peter_SSs]

Another pretty similar udf, but more compact.

Function MaxD(High As Range, Low As Range) As Double
  Dim i As Long, rws As Long
  Dim d As Double
  
  rws = High.Rows.Count
  For i = 1 To rws
    d = Application.Max(High.Resize(i)) - Application.Min(Low.Offset(i - 1).Resize(rws - i + 1))
    If d > MaxD Then MaxD = d
  Next i
End Function

Also, a slightly more compact version of Stephen's worksheet formula approach.

excelstreet.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	10	10		15	15		17	12		7	7
2	12	10					19	13
3	14	11					20	13
4	12	11					18	13
5	10	9
6	15	9
7	20	11
8	25	11
9	20	10
Diff

Cell Formulas
Range	Formula
D1	D1	=MaxD(A1:A9,B1:B9)
E1	E1	=MAX(SUBTOTAL(4,OFFSET(A1:A9,,,ROW(A1:A9)-ROW(A1)+1))-SUBTOTAL(5,OFFSET(B9,,,-1-ROW(B9)+ROW(B1:B9))))
J1	J1	=MaxD(G1:G4,H1:H4)
K1	K1	=MAX(SUBTOTAL(4,OFFSET(G1:G4,,,ROW(G1:G4)-ROW(G1)+1))-SUBTOTAL(5,OFFSET(H4,,,-1-ROW(H4)+ROW(H1:H4))))

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Max Difference Between Two Dynamic Ranges
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[excelstreet]

Fluff, thanks for bringing me up to speed in regards to the proper posting guidelines and forum rules in terms of cross-posting.

Stephen & Peter, thanks for helping out and providing not just the edits needed on my attempt in the OP but also other solutions as well! Spent some time researching the different functions and logic used in both of you all's solutions. Your solutions definitely helped expand not just my knowledge of excel but also what may be possible for workbooks.

For others who come across this post that may have similar needs to my request in the OP here are some links that have helped me better understand the solutions Stephen and Peter provided.

VBA
Set statement
- Particularly "The Dim, Private, Public, ReDim, and Static statements only declare a variable that refers to an object. No actual object is referred to until you use the Set statement to assign a specific object."
Range.Cells property
- Particularly "Because the default member of Range forwards calls with parameters to the Item property, you can specify the row and column index immediately after the Cells keyword instead of an explicit call to Item."

Formula
SUBTOTAL function
- Particularly the Function_num argument "The number 1-11 or 101-111 that specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded."
OFFSET function
- Particularly the Reference argument "The reference from which you want to base the offset."
- Additionally the Width argument "The width, in number of columns, that you want the returned reference to be. Width must be a positive number."

Thanks again everyone, glad to be here!

 

[Peter_SSs]

You're welcome. Glad we could help.