calculations with arrays help

[Matt S]

Hi all (again),

I have successfully made an array that labels my data into 4 modes. The 4
modes repeat continuously for fifty hours, so my labelarray labeled what mode
I'm in per second.

Now I have the arduous task of doing calculations on the array while it is
in a certain mode cycle. For example, I'd like to take the average of my
TemperatureArray while my LabelArray is in Mode 1 for this cycle and record
that cycle average in a new array. I do not want to do the average of all
Mode 1's, rather the individual averages of all cycles.

I've been sitting here for an hour trying to think of a conceptual way to do
this, but am completely stumped.

Any suggestions??
THANKS!
Matt

 

[Matt S]

Thanks Bernie... Here's a table:

Row.....Temp...Label
1.........123.....Mode 1
2.........242.....Mode 1
3.........292.....Mode 2
4.........317.....Mode 2
5.........300.....Mode 3
6.........250.....Mode 3
7.........219.....Mode 4
8.........200.....Mode 4
9.........215.....Mode 1
10........210.....Mode 1
11........190......Mode 2
12........275......Mode 2
etc

I want to average Mode 1 on rows 1 and 2 and save that to a 2-dimensional
array as Data_Array(cycle 1, calc 1). Data_Array(cycle 1, calc 2) would be
another calculation for that cycle. Data_Array(cycle 2, calc 1) would be the
average of rows 9 and 10. The temperature and the labels are both their own
arrays. With 50hours of data, I'd rather try to keep the formulas in the
array form.

THANK YOU SO MUCH!
Matt

 

[Matt S]

This is what I came up with, although the code doesn't work...The NumElements
doesn't make any sense at all, since this is a multi-dimensional array. I
guess it shows better what I'm trying to do, though.



'Populate Mode arrays with data to perform calculations
ReDim arrMode1(1 To LastRow, 1 To 100, 1 To 5)
ReDim arrMode2(1 To LastRow, 1 To 100, 1 To 5)
ReDim arrMode3(1 To LastRow, 1 To 100, 1 To 5)
ReDim arrMode4(1 To LastRow, 1 To 100, 1 To 5)

CountCycle = 0

For j = 8 To LastRow

If arrLabel(j) = "Mode 1" Then
If Not arrLabel(j - 1) = "Mode 1" Then
CountCycle = CountCycle + 1
Count1 = 1
Count2 = 0
Count3 = 0
Count4 = 0
Else
End If
arrMode1(CountCycle, Count1, 1) = arrBedT(j)
arrMode1(CountCycle, Count1, 2) = arrInletT(j)
arrMode1(CountCycle, Count1, 3) = arrRUEGO(j)

ElseIf arrLabel(j) = "Mode 2" Then
Count2 = Count2 + 1
arrMode2(CountCycle, Count2, 1) = arrBedT(j)
arrMode2(CountCycle, Count2, 2) = arrFUEGO(j)
arrMode2(CountCycle, Count2, 3) = arrRUEGO(j)

ElseIf arrLabel(j) = "Mode 3" Then
Count3 = Count3 + 1
arrMode3(CountCycle, Count3, 1) = arrBedT(j)
arrMode3(CountCycle, Count3, 2) = arrRUEGO(j)
arrMode3(CountCycle, Count3, 3) = arrFUEGO(j)
arrMode3(CountCycle, Count3, 4) = arrOxygen(j)

ElseIf arrLabel(j) = "Mode 4" Then
Count4 = Count4 + 1
arrMode4(CountCycle, Count4, 1) = arrTime(j)
arrMode4(CountCycle, Count4, 2) = arrBedT(j)
arrMode4(CountCycle, Count4, 3) = arrRUEGO(j)
arrMode4(CountCycle, Count4, 4) = arrOxygen(j)

Else
End If

Next j


''''''''''''''''''''''''''''''''
'Do calculations on new arrays '
''''''''''''''''''''''''''''''''

For i = 1 To Cycles

'Time at end of Mode 4
N = NumElements(arrMode4, 2)
arrValues(i, 1) = arrMode4(i, N, 1)

'Average Control Oxygen for Modes 3 and 4 - 5 sec after mode 3 begins
SumOxygen = 0
N = NumElements(arrMode3, 2)
M = NumElements(arrMode4, 2)

For j = 5 To N
SumOxygen = SumOxygen + arrMode3(i, j, 4)
Next j

For k = 1 To M
SumOxygen = SumOxygen + arrMode4(i, k, 4)
Next k

arrValues(i, 2) = SumOxygen / (N + M - 5)

'Front UEGO averaged for Mode 2 and 3 - 3 sec after start of Mode 2
SumFUEGO = 0
N = NumElements(arrMode2, 2)
M = NumElements(arrMode3, 2)

For j = 3 To N
SumFUEGO = SumFUEGO + arrMode2(i, j, 2)
Next j

For k = 1 To M
SumFUEGO = SumFUEGO + arrMode3(i, j, 3)
Next k

arrValues(i, 3) = SumFUEGO / (N + M - 3)

'Inlet Temp is average temperature at mode 1 - 10 last seconds
averaged
SumInletTemp = 0
N = NumElements(arrMode1, 2)

For j = (N - 10) To N
SumInletTemp = SumInletTemp + arrMode1(i, j, 2)
Next j

arrValues(i, 4) = SumInletTemp / 10

'T Max Bed T for all modes
M = NumElements(arrMode1, 2)
N = NumElements(arrMode2, 2)
O = NumElements(arrMode3, 2)
P = NumElements(arrMode4, 2)

MaxValue = 0

For j = 1 To M
If arrMode1(i, j, 1) > MaxValue Then
MaxValue = arrMode1(i, j, 1)
End If
Next j

For j = 1 To N
If arrMode2(i, j, 1) > MaxValue Then
MaxValue = arrMode2(i, j, 1)
End If
Next j

For j = 1 To O
If arrMode3(i, j, 1) > MaxValue Then
MaxValue = arrMode3(i, j, 1)
End If
Next j

For j = 1 To P
If arrMode4(i, j, 1) > MaxValue Then
MaxValue = arrMode4(i, j, 1)
End If
Next j

arrValues(i, 5) = MaxValue

'RUEGO peak during Modes 2 and 3
M = NumElements(arrMode2, 2)
N = NumElements(arrMode3, 2)

MaxValue = 0

For j = 1 To M
If arrMode2(i, j, 3) > MaxValue Then
MaxValue = arrMode2(i, j, 3)
End If
Next j

For j = 1 To N
If arrMode3(i, j, 2) > MaxValue Then
MaxValue = arrMode3(i, j, 2)
End If
Next j

arrValues(i, 6) = MaxValue

'R UEGO peak during Modes 4 and the next cycle's 1

M = NumElements(arrMode4, 2)
N = NumElements(arrMode1, 2)

MaxValue = 0

For j = 1 To M
If arrMode4(i, j, 3) > MaxValue Then
MaxValue = arrMode4(i, j, 3)
End If
Next j

For j = 1 To N
If arrMode1(i + 1, j, 3) > MaxValue Then
MaxValue = arrMode1(i + 1, j, 3)
End If
Next j

arrValues(i, 7) = MaxValue




ActiveSheet.Range("Y9:AF" & Cycles + 8).Value = arrValues

Next

 

[Bernie Deitrick]

Matt,

Below is a macro that I use to get cycle-based averages. I have modified it
to work with your specific labels (Mode 1, etc.), but with a data table laid
out like this, with variable lengths of modes, and as much data as you have.
Right now, there is an upper limit of 5 columns of data, for 4 modes, but
unlimited cycles (since the data array redims as needed).

Here is the data table layout, starting in A1

Mode Value1 Value2 Value3 Value4 Value5
Mode 1 1 1 1 1 1
Mode 2 2 2 2 2 2
Mode 3 3 3 3 3 3
Mode 3 3 3 3 3 3
Mode 3 7 7 7 7 7
Mode 3 7 7 7 7 7
Mode 3 1 1 1 1 1
Mode 3 1 1 1 1 1
Mode 4 4 4 4 4 4
Mode 4 4 4 4 4 4
Mode 1 1 1 1 1 1
Mode 1 1 1 1 1 1
Mode 1 1 1 1 1 1
Mode 1 1 1 1 1 1
Mode 2 2 2 2 2 2
Mode 3 3 3 3 3 3
Mode 3 3 3 3 3 3
Mode 3 7 7 7 7 7
Mode 3 7 7 7 7 7
Mode 3 1 1 1 1 1
Mode 3 1 1 1 1 1
Mode 4 4 4 4 4 4
Mode 4 4 4 4 4 4


My other assumptions are: the data sheet is named "DataSheet", the data
starts in row 2, the headers are in row 1, the mode values are in column A,
and you have 5 columns of data to average. (Any of these should be easy to
modify...)

The macro will create a sheet named "Averages", with the resulting averages
laid out by cycle and mode. You will need to reference Microsoft Scripting
Runtime.

Try it - you'll see what I mean. If you can't get it to work, contact me
privately, and I will send you a working example workbook.

HTH,
Bernie
MS Excel MVP

Add a reference to Microsoft Scripting Runtime.
Add a standard module to your workbook.
Insert the following code into the Module.

Option Base 1
Option Explicit

Sub GetAverages()
'First Index is mode (there are only 4 modes?)
'Second index is parameter (values to be averaged)
'Third Index is cycle, and can be increased as needed
'Value stored is average for cycle
Dim DataAvgArr() As Double
ReDim DataAvgArr(1 To 4, 1 To 5, 1 To 1)

'This code enters the modes into a dictionary to assign key values to
them
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Dict As Dictionary
Dim iDataCol As Integer
Dim iIndex As Integer
Dim FirstIndex As Integer
Dim myR As Long
Dim cycCount As Integer
Dim IndMatch As Boolean
Dim curIndex As Integer
Dim DataCount As Integer
Dim mySht As Worksheet
Dim keyArray As Variant

Set mySht = Worksheets("DataSheet")
Set Dict = New Dictionary

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Averages").Delete
Worksheets.Add
ActiveSheet.Name = "Averages"
Application.DisplayAlerts = True

'set compare mode
Dict.CompareMode = BinaryCompare
For iIndex = 1 To 4
Dict.Add Key:="Mode " & iIndex, Item:=iIndex
Next iIndex
'If your keys values aren't really named so nicely,
'you can add the names to the dictionary with code like this
'Dict.Add Key:="Name of mode 1", Item:=1
'Dict.Add Key:="Name of mode 2", Item:=2
'etc...

keyArray = Dict.Keys

'Then get the averages of the next 5 columns
'Based on mode in column 1
cycCount = 1
iIndex = Dict.Item(mySht.Cells(2, 1).Value)
FirstIndex = iIndex
curIndex = iIndex
IndMatch = True

For iDataCol = 1 To 5 'Actually, data in columns B to F, so add 1
DataAvgArr(iIndex, iDataCol, cycCount) = _
mySht.Cells(2, iDataCol + 1).Value
Next iDataCol
DataCount = 1

For myR = 3 To mySht.Cells(Rows.Count, 1).End(xlUp).Row
iIndex = Dict.Item(mySht.Cells(myR, 1).Value)
If iIndex <> curIndex Then
For iDataCol = 1 To 5
DataAvgArr(curIndex, iDataCol, cycCount) = _
DataAvgArr(curIndex, iDataCol, cycCount) / DataCount
Next iDataCol
curIndex = iIndex
DataCount = 0
End If

If iIndex <> FirstIndex Then IndMatch = False
If Not IndMatch And iIndex = FirstIndex Then
cycCount = cycCount + 1
IndMatch = True
ReDim Preserve DataAvgArr(1 To 4, 1 To 5, 1 To cycCount)
End If
DataCount = DataCount + 1
For iDataCol = 1 To 5
DataAvgArr(iIndex, iDataCol, cycCount) = _
DataAvgArr(iIndex, iDataCol, cycCount) + _
mySht.Cells(myR, iDataCol + 1).Value
Next iDataCol
Next myR

For iDataCol = 1 To 5
DataAvgArr(curIndex, iDataCol, cycCount) = _
DataAvgArr(curIndex, iDataCol, cycCount) / DataCount
Next iDataCol

With Worksheets("Averages")
For i = 1 To 4
For j = 1 To cycCount
.Cells(1 + (j - 1) * 6, 1).Value = "Cycle " & j
.Cells(1 + (j - 1) * 6, 2).Resize(1, 5).Value = _
mySht.Cells(1, 2).Resize(1, 5).Value
.Cells(i + (j - 1) * 6 + 1, 1).Value = keyArray(i - 1)
For k = 1 To 5
.Cells(i + (j - 1) * 6 + 1, k + 1).Value = DataAvgArr(i,
k, j)
Next k
Next j
Next i
End With
End Sub