Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Min Function on 2D Array
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 553
Default Min Function on 2D Array
Can't seem to get this to work. I am trying to calculate the min value for
the second column in an array. The printout from my Immediate Window tells
me this value should be 2. However the following line returns a value of 1:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

Sub MakeSenseOfArrays()
Dim Array1 As Variant
Dim MinRow As Integer

ReDim Array1(0 To 1, 0 To 2)
Array1(0, 0) = 1
Array1(0, 1) = 2
Array1(0, 2) = 3
Array1(1, 0) = 10
Array1(1, 1) = 20
Array1(1, 2) = 30

For X = 0 To UBound(Array1, 1)
Debug.Print Array1(X, 0) & " " & Array1(X, 1) & " " & Array1(X, 2)
Next

MinRow = Application.WorksheetFunction.Min(Array1, 2)

End Sub


Immediate Window:
1 2 3
10 20 30
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 91
Default Min Function on 2D Array
On Dec 11, 11:06 am, ExcelMonkey
wrote:
Can't seem to get this to work. I am trying to calculate the min value for
the second column in an array. The printout from my Immediate Window tells
me this value should be 2. However the following line returns a value of 1:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

Sub MakeSenseOfArrays()
Dim Array1 As Variant
Dim MinRow As Integer

ReDim Array1(0 To 1, 0 To 2)
Array1(0, 0) = 1
Array1(0, 1) = 2
Array1(0, 2) = 3
Array1(1, 0) = 10
Array1(1, 1) = 20
Array1(1, 2) = 30

For X = 0 To UBound(Array1, 1)
Debug.Print Array1(X, 0) & " " & Array1(X, 1) & " " & Array1(X, 2)
Next

MinRow = Application.WorksheetFunction.Min(Array1, 2)

End Sub

Immediate Window:
1 2 3
10 20 30

Is 1 not the minimum value of the second column? Am I missing
something? 2 is not the minimum of either column.
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 91
Default Min Function on 2D Array
On Dec 11, 11:06 am, ExcelMonkey
wrote:
Can't seem to get this to work. I am trying to calculate the min value for
the second column in an array. The printout from my Immediate Window tells
me this value should be 2. However the following line returns a value of 1:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

Sub MakeSenseOfArrays()
Dim Array1 As Variant
Dim MinRow As Integer

ReDim Array1(0 To 1, 0 To 2)
Array1(0, 0) = 1
Array1(0, 1) = 2
Array1(0, 2) = 3
Array1(1, 0) = 10
Array1(1, 1) = 20
Array1(1, 2) = 30

For X = 0 To UBound(Array1, 1)
Debug.Print Array1(X, 0) & " " & Array1(X, 1) & " " & Array1(X, 2)
Next

MinRow = Application.WorksheetFunction.Min(Array1, 2)

End Sub

Immediate Window:
1 2 3
10 20 30

Oh wait, I think I get what's happening here. The min function used
in the manner you've used it in will return 1 because there's a value
in the array less than 2 (the second argument in the min function).
If you were to change the 1 to 100 when filling the array with values,
the min function would return 2. Basically it's finding the lowest
value in the array and using that for the 1st argument of the min
function. Then it's comparing it to the 2nd argument in the min
function (2) and telling you which one of the two arguments is less.

I'd recomment just looping through the second column to find out the
lowest value in it.
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 783
Default Min Function on 2D Array
ExcelMonkey wrote:
Can't seem to get this to work. I am trying to calculate the min value for
the second column in an array. The printout from my Immediate Window tells
me this value should be 2. However the following line returns a value of 1:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

Sub MakeSenseOfArrays()
Dim Array1 As Variant
Dim MinRow As Integer

ReDim Array1(0 To 1, 0 To 2)
Array1(0, 0) = 1
Array1(0, 1) = 2
Array1(0, 2) = 3
Array1(1, 0) = 10
Array1(1, 1) = 20
Array1(1, 2) = 30

For X = 0 To UBound(Array1, 1)
Debug.Print Array1(X, 0) & " " & Array1(X, 1) & " " & Array1(X, 2)
Next

MinRow = Application.WorksheetFunction.Min(Array1, 2)

End Sub


Immediate Window:
1 2 3
10 20 30

MinRow = Application.WorksheetFunction.Min(Index(Array1,,2) )

Alan Beban
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 553
Default Min Function on 2D Array
Allan, I cannot get this line to work:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

I keep getting a "Sub of Function not defined" error.

I event tried the following and recieved an "Arguement not optional" error

MinRow =
Application.WorksheetFunction.Min(Application.Work sheetFunction.Index(Array1,
, 2))

????

EM


"Alan Beban" wrote:

ExcelMonkey wrote:
Can't seem to get this to work. I am trying to calculate the min value for
the second column in an array. The printout from my Immediate Window tells
me this value should be 2. However the following line returns a value of 1:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

Sub MakeSenseOfArrays()
Dim Array1 As Variant
Dim MinRow As Integer

ReDim Array1(0 To 1, 0 To 2)
Array1(0, 0) = 1
Array1(0, 1) = 2
Array1(0, 2) = 3
Array1(1, 0) = 10
Array1(1, 1) = 20
Array1(1, 2) = 30

For X = 0 To UBound(Array1, 1)
Debug.Print Array1(X, 0) & " " & Array1(X, 1) & " " & Array1(X, 2)
Next

MinRow = Application.WorksheetFunction.Min(Array1, 2)

End Sub


Immediate Window:
1 2 3
10 20 30

MinRow = Application.WorksheetFunction.Min(Index(Array1,,2) )

Alan Beban

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7,247
Default Min Function on 2D Array
How have you declared and loaded the Array1 variable? The following example
code works as expected.

Dim Arr(1 To 3, 1 To 2) As Double
Dim Min As Variant

Arr(1, 1) = 3
Arr(1, 2) = 5
Arr(2, 1) = 7
Arr(2, 2) = 9
Arr(3, 1) = 11
Arr(3, 2) = 12

Min = Application.WorksheetFunction.Min(Arr, 2)
If IsError(Min) = True Then
Debug.Print "Error: " & CStr(Min)
Else
Debug.Print "Min: " & CStr(Min)
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)



"ExcelMonkey" wrote in message
...
Allan, I cannot get this line to work:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

I keep getting a "Sub of Function not defined" error.

I event tried the following and recieved an "Arguement not optional" error

MinRow =
Application.WorksheetFunction.Min(Application.Work sheetFunction.Index(Array1,
, 2))

????

EM


"Alan Beban" wrote:

ExcelMonkey wrote:
Can't seem to get this to work. I am trying to calculate the min value
for
the second column in an array. The printout from my Immediate Window
tells
me this value should be 2. However the following line returns a value
of 1:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

Sub MakeSenseOfArrays()
Dim Array1 As Variant
Dim MinRow As Integer

ReDim Array1(0 To 1, 0 To 2)
Array1(0, 0) = 1
Array1(0, 1) = 2
Array1(0, 2) = 3
Array1(1, 0) = 10
Array1(1, 1) = 20
Array1(1, 2) = 30

For X = 0 To UBound(Array1, 1)
Debug.Print Array1(X, 0) & " " & Array1(X, 1) & " " & Array1(X,
2)
Next

MinRow = Application.WorksheetFunction.Min(Array1, 2)

End Sub


Immediate Window:
1 2 3
10 20 30

MinRow = Application.WorksheetFunction.Min(Index(Array1,,2) )

Alan Beban


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 553
Default Min Function on 2D Array
Sub MakeSenseOfArrays()
Dim Array1 As Variant
Dim MinRow As Integer

ReDim Array1(0 To 1, 0 To 2)
Array1(0, 0) = 1
Array1(0, 1) = 2
Array1(0, 2) = 3
Array1(1, 0) = 10
Array1(1, 1) = 20
Array1(1, 2) = 30

For X = 0 To UBound(Array1, 1)
'Print Rows of fist column
Debug.Print Array1(X, 0) & " " & Array1(X, 1) & " " & Array1(X, 2)
Next

MinRow = Application.WorksheetFunction.Min(Index(Array1, , 2))

End Sub

"Chip Pearson" wrote:

How have you declared and loaded the Array1 variable? The following example
code works as expected.

Dim Arr(1 To 3, 1 To 2) As Double
Dim Min As Variant

Arr(1, 1) = 3
Arr(1, 2) = 5
Arr(2, 1) = 7
Arr(2, 2) = 9
Arr(3, 1) = 11
Arr(3, 2) = 12

Min = Application.WorksheetFunction.Min(Arr, 2)
If IsError(Min) = True Then
Debug.Print "Error: " & CStr(Min)
Else
Debug.Print "Min: " & CStr(Min)
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)



"ExcelMonkey" wrote in message
...
Allan, I cannot get this line to work:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

I keep getting a "Sub of Function not defined" error.

I event tried the following and recieved an "Arguement not optional" error

MinRow =
Application.WorksheetFunction.Min(Application.Work sheetFunction.Index(Array1,
, 2))

????

EM


"Alan Beban" wrote:

ExcelMonkey wrote:
Can't seem to get this to work. I am trying to calculate the min value
for
the second column in an array. The printout from my Immediate Window
tells
me this value should be 2. However the following line returns a value
of 1:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

Sub MakeSenseOfArrays()
Dim Array1 As Variant
Dim MinRow As Integer

ReDim Array1(0 To 1, 0 To 2)
Array1(0, 0) = 1
Array1(0, 1) = 2
Array1(0, 2) = 3
Array1(1, 0) = 10
Array1(1, 1) = 20
Array1(1, 2) = 30

For X = 0 To UBound(Array1, 1)
Debug.Print Array1(X, 0) & " " & Array1(X, 1) & " " & Array1(X,
2)
Next

MinRow = Application.WorksheetFunction.Min(Array1, 2)

End Sub


Immediate Window:
1 2 3
10 20 30

MinRow = Application.WorksheetFunction.Min(Index(Array1,,2) )

Alan Beban


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7,247
Default Min Function on 2D Array
The code works as expected for me once I declared the variable X and change
"Index" to "Application.Index".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"ExcelMonkey" wrote in message
...
Sub MakeSenseOfArrays()
Dim Array1 As Variant
Dim MinRow As Integer

ReDim Array1(0 To 1, 0 To 2)
Array1(0, 0) = 1
Array1(0, 1) = 2
Array1(0, 2) = 3
Array1(1, 0) = 10
Array1(1, 1) = 20
Array1(1, 2) = 30

For X = 0 To UBound(Array1, 1)
'Print Rows of fist column
Debug.Print Array1(X, 0) & " " & Array1(X, 1) & " " & Array1(X, 2)
Next

MinRow = Application.WorksheetFunction.Min(Index(Array1, , 2))

End Sub

"Chip Pearson" wrote:

How have you declared and loaded the Array1 variable? The following
example
code works as expected.

Dim Arr(1 To 3, 1 To 2) As Double
Dim Min As Variant

Arr(1, 1) = 3
Arr(1, 2) = 5
Arr(2, 1) = 7
Arr(2, 2) = 9
Arr(3, 1) = 11
Arr(3, 2) = 12

Min = Application.WorksheetFunction.Min(Arr, 2)
If IsError(Min) = True Then
Debug.Print "Error: " & CStr(Min)
Else
Debug.Print "Min: " & CStr(Min)
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)



"ExcelMonkey" wrote in message
...
Allan, I cannot get this line to work:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

I keep getting a "Sub of Function not defined" error.

I event tried the following and recieved an "Arguement not optional"
error

MinRow =
Application.WorksheetFunction.Min(Application.Work sheetFunction.Index(Array1,
, 2))

????

EM


"Alan Beban" wrote:

ExcelMonkey wrote:
Can't seem to get this to work. I am trying to calculate the min
value
for
the second column in an array. The printout from my Immediate
Window
tells
me this value should be 2. However the following line returns a
value
of 1:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

Sub MakeSenseOfArrays()
Dim Array1 As Variant
Dim MinRow As Integer

ReDim Array1(0 To 1, 0 To 2)
Array1(0, 0) = 1
Array1(0, 1) = 2
Array1(0, 2) = 3
Array1(1, 0) = 10
Array1(1, 1) = 20
Array1(1, 2) = 30

For X = 0 To UBound(Array1, 1)
Debug.Print Array1(X, 0) & " " & Array1(X, 1) & " " &
Array1(X,
2)
Next

MinRow = Application.WorksheetFunction.Min(Array1, 2)

End Sub


Immediate Window:
1 2 3
10 20 30

MinRow = Application.WorksheetFunction.Min(Index(Array1,,2) )

Alan Beban



Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 783
Default Min Function on 2D Array
ExcelMonkey wrote:
Allan, I cannot get this line to work:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

I keep getting a "Sub of Function not defined" error.

I event tried the following and recieved an "Arguement not optional" error

MinRow =
Application.WorksheetFunction.Min(Application.Work sheetFunction.Index(Array1,
, 2))

Application.WorksheetFunction.Min(Application.Inde x(Array1,,2)

Sorry, I left out the second "Application"; it doesn't like

Application.WorksheetFunction.Index(...)

Alan Beban
Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 783
Default Min Function on 2D Array
ExcelMonkey wrote:
Allan, I cannot get this line to work:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

I keep getting a "Sub of Function not defined" error.

I event tried the following and recieved an "Arguement not optional" error

MinRow =
Application.WorksheetFunction.Min(Application.Work sheetFunction.Index(Array1,
, 2))


As I said in my last post

Application.WorksheetFunction.Min(Application.Inde x(Array1,,2))

It also works with

Application.WorksheetFunction.Min(Application.Work sheetFunction(Array1,0,2))

Alan Beban

I believe that

Application.WorksheetFunction.Min(Array1, 2)
returns the minimum of all the elements in Array1 and 2; i.e., if Array1
is {1,6,7;8,4,3}, then it returns the minimum of 1,6,7,8,4,3 and 2.

Alan Beban
Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 553
Default Min Function on 2D Array
Thanks Alan.

"Alan Beban" wrote:

ExcelMonkey wrote:
Allan, I cannot get this line to work:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

I keep getting a "Sub of Function not defined" error.

I event tried the following and recieved an "Arguement not optional" error

MinRow =
Application.WorksheetFunction.Min(Application.Work sheetFunction.Index(Array1,
, 2))

Application.WorksheetFunction.Min(Application.Inde x(Array1,,2)

Sorry, I left out the second "Application"; it doesn't like

Application.WorksheetFunction.Index(...)

Alan Beban

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Array / Function HELP!!! chris[_2_] Excel Worksheet Functions 2 June 4th 08 08:51 PM
OR function in array-entered IF function veggies27 Excel Worksheet Functions 8 March 11th 08 06:32 PM
Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order) Keith R[_2_] Excel Programming 3 November 13th 07 04:08 PM
need some help with an array function Lorin Excel Discussion (Misc queries) 1 January 3rd 06 02:37 AM
Help with an array function malik641 New Users to Excel 4 June 10th 05 05:09 PM


All times are GMT +1. The time now is 08:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement