Counting for Minimum Difference Value

  • Thread starter Thread starter Michael168
  • Start date Start date
M

Michael168

e.g. In a worksheet,

Cells

A1=5
B1=3
C1=4
D1=8
E1=1

In G1 for min difference of sum in the range = C1-B1 = 1
In H1 for max difference of sum in the range = D1-E1 = 7

For maximum difference, the max formula can be used but what about the
minimun difference because the min formula cannot give the correct
answer.

How to do this in the range (A1:E1)? Cells value in the are
changing constantly.

Can someone help me?

Thank you.
 
Michael,

How about

=MIN(ABS(A1:D1-B1:E1))

as an array formula, entered with Ctrl-Shift-Enter.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Michael168 said:
e.g. In a worksheet,

Cells [ed.] A1=5, B1=3, C1=4, D1=8, E1=1

In G1 for min difference of sum in the range = C1-B1 = 1
In H1 for max difference of sum in the range = D1-E1 = 7

For maximum difference, the max formula can be used but what about the
minimun difference because the min formula cannot give the correct
answer.

How to do this in the range (A1:E1)? Cells value in the are
changing constantly.
Click to expand...

Michael,

How about

=MIN(ABS(A1:D1-B1:E1))

as an array formula, entered with Ctrl-Shift-Enter.
Click to expand...

Close, but I think that only returns the smallest difference between
neighbouring cells. E.g. swapping C1/D1 in the OP's array {5,3,4,8,1}
giving {5,3,8,4,1}, your formula returns 2, the difference between 5 and
3.

Below is a VBA function which should do it. Put =MinDiff(A1:E1) into G1.
============================================================
Function MinDiff(theRange As Range) As Double

Dim i As Long, j As Long
Dim theDiff As Double
If theRange.Count < 2 Then
MsgBox "You must select a range of at least 2 cells.", _
vbExclamation + vbOKOnly, "Function MinDiff"
Return
End If
MinDiff = Application.WorksheetFunction.Max(theRange)
For i = 1 To theRange.Count - 1
For j = i + 1 To theRange.Count
theDiff = Abs(theRange.Item(i) - theRange.Item(j))
If theDiff < MinDiff Then MinDiff = theDiff
Next j
Next i
End Function
============================================================
 
Michael,

That's exactly as I read it. As we have to in this game, I made some
assumptions based upon what was said. Although he didn't explicitly say
adjacent cells, the OP said that MAX was easy, but MIN didn't work. I could
see how he could get the max value from any pairing using MAX and MIN, but
not MAX. From this I surmised that he was comparing adjacent cells. Probably
off target, but the full story is rarely there.


Bob

PS you boys were not very convincing today!


Michael Bednarek said:
Michael168 said:
e.g. In a worksheet,

Cells [ed.] A1=5, B1=3, C1=4, D1=8, E1=1

In G1 for min difference of sum in the range = C1-B1 = 1
In H1 for max difference of sum in the range = D1-E1 = 7

For maximum difference, the max formula can be used but what about the
minimun difference because the min formula cannot give the correct
answer.

How to do this in the range (A1:E1)? Cells value in the are
changing constantly.
Click to expand...

Michael,

How about

=MIN(ABS(A1:D1-B1:E1))

as an array formula, entered with Ctrl-Shift-Enter.
Click to expand...

Close, but I think that only returns the smallest difference between
neighbouring cells. E.g. swapping C1/D1 in the OP's array {5,3,4,8,1}
giving {5,3,8,4,1}, your formula returns 2, the difference between 5 and
3.

Below is a VBA function which should do it. Put =MinDiff(A1:E1) into G1.
============================================================
Function MinDiff(theRange As Range) As Double

Dim i As Long, j As Long
Dim theDiff As Double
If theRange.Count < 2 Then
MsgBox "You must select a range of at least 2 cells.", _
vbExclamation + vbOKOnly, "Function MinDiff"
Return
End If
MinDiff = Application.WorksheetFunction.Max(theRange)
For i = 1 To theRange.Count - 1
For j = i + 1 To theRange.Count
theDiff = Abs(theRange.Item(i) - theRange.Item(j))
If theDiff < MinDiff Then MinDiff = theDiff
Next j
Next i
End Function
============================================================
Click to expand...
 
Thank you to both of you for the formula and functions code.
I actually needed a module code to exceute for the whole range of the
worksheet automatically write the output in columns G & H.

Regards

Michael said:
e.g. In a worksheet,

Cells [ed.] A1=5, B1=3, C1=4, D1=8, E1=1

In G1 for min difference of sum in the range = C1-B1 = 1
In H1 for max difference of sum in the range = D1-E1 = 7

For maximum difference, the max formula can be used but what about the
minimun difference because the min formula cannot give the correct
answer.

How to do this in the range (A1:E1)? Cells value in the are
changing constantly.
Click to expand...

Michael,

How about

=MIN(ABS(A1:D1-B1:E1))

as an array formula, entered with Ctrl-Shift-Enter.
Click to expand...
Click to expand...

Close, but I think that only returns the smallest difference between
neighbouring cells. E.g. swapping C1/D1 in the OP's array
{5,3,4,8,1}
giving {5,3,8,4,1}, your formula returns 2, the difference between 5
and
3.

Below is a VBA function which should do it. Put =MinDiff(A1:E1) into
G1.
============================================================
Function MinDiff(theRange As Range) As Double

Dim i As Long, j As Long
Dim theDiff As Double
If theRange.Count < 2 Then
MsgBox "You must select a range of at least 2 cells.", _
vbExclamation + vbOKOnly, "Function MinDiff"
Return
End If
MinDiff = Application.WorksheetFunction.Max(theRange)
For i = 1 To theRange.Count - 1
For j = i + 1 To theRange.Count
theDiff = Abs(theRange.Item(i) - theRange.Item(j))
If theDiff < MinDiff Then MinDiff = theDiff
Next j
Next i
End Function
============================================================
Click to expand...
 
Back
Top