Application.WorksheetFunction.Min

[vba317]

Hello,
I have the current code:
LowMin(1, LowMinCount) = Application.WorksheetFunction.Min(LowCellValue(1, LowCellCount), LowCellValue(1, LowCellCount + 1), LowCellValue(1, LowCellCount + 2), LowCellValue(1, LowCellCount + 3), LowCellValue(1, LowCellCount + 4), LowCellValue(1, LowCellCount + 5), LowCellValue(1, LowCellCount + 6), LowCellValue(1, LowCellCount + 7))

I am running this code within a loop of 8. It works fine as long as I have an amount of rows that is even with 8. But If I have for example 37 rows than I get an error. Is there a way to rewrite this code so I can sample either 8 rows or just the remaining rows?

 

[SkipVought]

It seems to me that you are NOT sampling ROWS. You are sampling ARRAY ELEMENTS. Yes?

Just want to be sure what we are addressing.

Why not simply loop thru your array, to determine the minimum value?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

You are right. I am looping through an array. I am trying to loop through the array to get the minimum value with the following code:

For LowLoopCount = LowLoopStart To LowLoopEnd
If LowLoopCount = 1 Then LowLoopCount = 2

LowCellValue(1, LowLoopCount) = Range("D" & LowLoopCount)

If LowCellCount = 0 Then LowCellCount = 2
If LowLoopCount = 2 Then LowMinCount = 1

If LowLoopCount / 10 = Int(LowLoopCount / 10) Then LowCellCount = LowCellCount + 8
If LowLoopCount / 10 = Int(LowLoopCount / 10) Then LowMinCount = LowMinCount + 1

LowMin(1, LowMinCount) = Application.WorksheetFunction.Min(LowCellValue(1, LowCellCount), LowCellValue(1, LowCellCount + 1), LowCellValue(1, LowCellCount + 2), LowCellValue(1, LowCellCount + 3), LowCellValue(1, LowCellCount + 4), LowCellValue(1, LowCellCount + 5), LowCellValue(1, LowCellCount + 6), LowCellValue(1, LowCellCount + 7))


If LowCellRange > 1 And IsEmpty(LowCellValue(1, LowCellRange)) Then Exit Sub
If LowLoopCount > 8 And LowCellValue(1, LowLoopCount) = LowMin(1, LowMinCount) Then Range("H" & LowLoopCount).Value = Range("D" & LowLoopCount)
If LowLoopCount > 8 And LowCellValue(1, LowLoopCount) = LowMin(1, LowMinCount) Then Range("J" & LowLoopCount).Value = LowLoopCount
If LowLoopCount > 8 And LowCellValue(1, LowLoopCount) = LowMin(1, LowMinCount) Then HighLoopCount = HighLoopCount + 1
If LowLoopCount > 8 And LowCellValue(1, LowLoopCount) = LowMin(1, LowMinCount) And HighLoopCount Mod 2 <> 0 Then HighStart = LowLoopCount + 1
If LowLoopCount > 8 And LowCellValue(1, LowLoopCount) = LowMin(1, LowMinCount) And HighLoopCount Mod 2 <> 0 Then HighCellValue(1, HighLoopCount) = HighStart
If LowLoopCount > 8 And LowCellValue(1, LowLoopCount) = LowMin(1, LowMinCount) And HighLoopCount Mod 2 = 0 Then HighEnd = LowLoopCount - 1
If LowLoopCount > 8 And LowCellValue(1, LowLoopCount) = LowMin(1, LowMinCount) And HighLoopCount Mod 2 = 0 Then HighCellValue(1, HighLoopCount) = HighEnd


Next LowLoopCount

 

[SkipVought]

wow! Looks like a Rube Goldberg solution to me.

Can't really tell what you are trying to accomplish. You're loading your array here...

LowCellValue(1, LowLoopCount) = Range("D" & LowLoopCount)

and then using the MIN spreadsheet function on the array???

Why would you not use it DIRECTLY on the range in column D, in one swell foop (fell swoop)?

MinValue = Range("D" & LowLoopStart & ":D" & LowLoopEnd)

Short of this directly helping, you REALLY need to explain in detail WHAT you are trying to do and do not try to explain HOW you think it ought to be accomplished.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

What I am trying to accomplish is this. In this example I have 37 rows of data. The low values are stored in the D column. I want to calculate the lowest value of the cells D2 - D10 in a worksheet. Say in this example the minimum value is what is in D9. When the code loops through the 2nd time I want it to find the minimum value of D11 - D19 say it is in D17. When it loops through the 3rd time the min value for D18 - D25 the min value is in D19. 4th time the min value for D26 - D33, the min value is in 32. 5th time the lowest value for D34 - D37, the min value is in cell D34.

Once this is done the second thing I would like to accomplish is to calulate the highest value using the lowest values as a base. So for example the high values are stored in the C column. 1st loop. Calculate the max values for 1st loop max value C12(D11+1) - C18 (D19-1),max value is in C11 2nd loop max value C19(D18+1) - C24(D25-1),max value is in C21. 3rd loop C27(D26+1) - C32(D33-1),max value is in C32. 4th loop C35(D34+1) - C36 (D37-1), max value is in C35.
This is the first step in what I am trying to do.

 

[MajP]

I do not do much work in Excel by I think you could build a simple function you can use on the worksheet to populate the min values within a group. Then you can use it anywhere. Make it flexible so you can define the range (column) and the groupsize.

Public Function MinInGroup(ValueRange As Range, X As Range, groupsize As Integer) As Single
 Dim MyGroup As Integer
 Dim cl As Range
 Dim ItemNumber As Integer
 Dim GroupStart As Integer
 Dim GroupEnd As Integer
 Dim GroupRange As Range
 Dim GroupStartCellAddress As String
 Dim GroupEndCellAddress As String
 
 'Determine the cells location in the range
 For Each cl In ValueRange.Cells
   ItemNumber = ItemNumber + 1
   If cl.Address = X.Address Then Exit For
 Next cl
 'Determine which group of the range the cell is in
 MyGroup = GroupNumber(ItemNumber, groupsize)
 'Determine the start and end locations of that group in the range
 GroupStart = (MyGroup - 1) * groupsize + 1
 GroupEnd = MyGroup * groupsize
 'Account for the last group ending at the last cell in range
 If GroupEnd > ValueRange.Cells.Count Then GroupEnd = ValueRange.Cells.Count
 'Get the address of the group in the range
 GroupStartCellAddress = ValueRange.Cells(GroupStart, 1).Address
 GroupEndCellAddress = ValueRange.Cells(GroupEnd, 1).Address
 'Get the min value for the group
 
 Set GroupRange = Range(GroupStartCellAddress, GroupEndCellAddress)
  
  MinInGroup = WorksheetFunction.Min(GroupRange)
End Function
Public Function GroupNumber(ItemNumber As Integer, groupsize As Integer) As Integer
  GroupNumber = WorksheetFunction.Ceiling(ItemNumber / groupsize, 1)
End Function

so to use for this example
in the first cell "=minInGroup(D$2$37,D2,8)". Then just drag it down.

Values	        Min in Group
13.77257663	3.86200285
27.08834980	3.86200285
15.72006446	3.86200285
4.916261404	3.86200285
9.765491206	3.86200285
17.36787113	3.86200285
3.862002796	3.86200285
16.26830222	3.86200285
9.290362315	2.87681818
21.34209319	2.87681818
3.504709208	2.87681818
4.199099471	2.87681818
2.876818220	2.87681818
10.82107243	2.87681818
5.631523539	2.87681818
13.18481176	2.87681818
0.598837056	0.571513057
19.72197822	0.571513057
23.24042679	0.571513057
29.10952686	0.571513057
2.119492543	0.571513057
14.98786444	0.571513057
15.61579683	0.571513057
0.571513069	0.571513057
29.88284861	0.833273232
11.49953590	0.833273232
14.25138633	0.833273232
21.42201582	0.833273232
7.830967085	0.833273232
21.42622495	0.833273232
0.833273257	0.833273232
19.09109394	0.833273232
25.41927546	1.240452647
24.87208092	1.240452647
1.240452589	1.240452647

 

[MajP]

I do not understand the rest of the post, but there is something about max values. So you can make the function a little more generic and pass in the type of value you want back over the group.

Public Function ValueInGroup(ValueRange As Range, X As Range, GroupSize As Integer, FunctionType As String) As Single
 Dim MyGroup As Integer
 Dim cl As Range
 Dim ItemNumber As Integer
 Dim GroupStart As Integer
 Dim GroupEnd As Integer
 Dim GroupRange As Range
 Dim GroupStartCellAddress As String
 Dim GroupEndCellAddress As String
 
 'Determine the cells location in the range
 For Each cl In ValueRange.Cells
   ItemNumber = ItemNumber + 1
   If cl.Address = X.Address Then Exit For
 Next cl
 'Determine which group of the range the cell is in
 MyGroup = GroupNumber(ItemNumber, GroupSize)
 'Determine the start and end locations of that group in the range
 GroupStart = (MyGroup - 1) * GroupSize + 1
 GroupEnd = MyGroup * GroupSize
 'Account for the last group ending at the last cell in range
 If GroupEnd > ValueRange.Cells.Count Then GroupEnd = ValueRange.Cells.Count
 'Get the address of the group in the range
 GroupStartCellAddress = ValueRange.Cells(GroupStart, 1).Address
 GroupEndCellAddress = ValueRange.Cells(GroupEnd, 1).Address
 'Get the min value for the group
 
 Set GroupRange = Range(GroupStartCellAddress, GroupEndCellAddress)
  Select Case FunctionType
  Case "Min"
    ValueInGroup = WorksheetFunction.Min(GroupRange)
  Case "Max"
    ValueInGroup = WorksheetFunction.Max(GroupRange)
  Case "Avg"
    ValueInGroup = WorksheetFunction.Average(GroupRange)
  Case "STD"
    ValueInGroup = WorksheetFunction.StDev(GroupRange)
   End Select
   
End Function

Values	Min in Group	MaxInGroup	AvgInGroup
13.77257663	3.86200285	27.0883503	13.59511471
27.0883498	3.86200285	27.0883503	13.59511471
15.72006446	3.86200285	27.0883503	13.59511471
4.916261404	3.86200285	27.0883503	13.59511471
9.765491206	3.86200285	27.0883503	13.59511471
17.36787113	3.86200285	27.0883503	13.59511471
3.862002796	3.86200285	27.0883503	13.59511471
16.26830222	3.86200285	27.0883503	13.59511471
9.290362315	2.87681818	21.34209251	8.856310844
21.34209319	2.87681818	21.34209251	8.856310844
3.504709208	2.87681818	21.34209251	8.856310844
4.199099471	2.87681818	21.34209251	8.856310844
2.87681822	2.87681818	21.34209251	8.856310844
10.82107243	2.87681818	21.34209251	8.856310844
5.631523539	2.87681818	21.34209251	8.856310844
13.18481176	2.87681818	21.34209251	8.856310844