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.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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" wrote in message
...
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.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

On Sat, 11 Oct 2003 10:50:00 +0100, "Bob Phillips"
wrote in
microsoft.public.excel.programming:

"Michael168" wrote in message
...
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.

Michael,

How about

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

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

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 Bednarek, IT Manager, TGM Ltd, Brisbane, Australia
http://mcmbednarek.tripod.com/ "POST NO BILLS"

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" wrote in message
...
On Sat, 11 Oct 2003 10:50:00 +0100, "Bob Phillips"
wrote in
microsoft.public.excel.programming:

"Michael168" wrote in message
...
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.

Michael,

How about

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

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

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 Bednarek, IT Manager, TGM Ltd, Brisbane, Australia
http://mcmbednarek.tripod.com/ "POST NO BILLS"

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 Bednarek wrote:
*On Sat, 11 Oct 2003 10:50:00 +0100, "Bob Phillips"
wrote in
microsoft.public.excel.programming:

"Michael168" wrote in
message
...
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.

Michael,

How about

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

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

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 Bednarek, IT Manager, TGM Ltd, Brisbane, Australia
http://mcmbednarek.tripod.com/ "POST NO BILLS" *



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/