Average and plot help - NEWBIE

[black02ss]

I don't even know where to start. I know what I want to accomplish, but don't know where to look or properly explain it. Here is the data I have and a "example" of what I want to do. I need to filter the data, average it for a certain cell and then plot it to a table.

I am new to writing code so any help and information that I can get will be greatly appreciated.

Chad

http://home.insightbb.com/~black02ss/Help.zip

average the LTFT for RPM 0-400 and Map 0-15
average the LTFT for RPM 400-800 and map 0-15
average the LTFT for RPM 800-1200 and map 0-15.....

This continues all the way through the table

Then we do the same thing, but for map
average the LTFT for RPM 0-400 and Map 15-20
average the LTFT for RPM 0-400 and map 20-25
average the LTFT for RPM 0-400 and map 25-30
average the LTFT for RPM 0-400 and map 30-35

All of the data would be outputed into the table in the zip file above.

you can see if I tried to do this manually and had to sort through all the data, I would be here forever. I hope this helps.

[Killian]

Hi there,

hopefully this isn't too late.
It wasn't really clear exactly what you wanted here - I've assumed that the LTFT refers to the data for "Long Term Fuel Trim Bank 1 (SAE) %", although it could be for bank 2 or both or neither???
I've gone ahead anyway so at least you can adjust the code accordingly
IMPORTANT NOTE: I only think this is right. You should check my logic and maths, perhaps against some previous results, before you use it for real!!!

Drop this code into your workbook and run "AnalyzeData" and see what happens...


Sub AnalyzeData()
Dim arrVals(1 To 19, 1 To 20) As Integer 'array to hold accumalated values
Dim arrCount(1 To 19, 1 To 20) As Integer 'array to hold count of values
Dim rngLTFT As Range 'range of values to work with
Dim OutTabHomeCell As Range 'reference cell for output table
'set our reference cell for output table
Set OutTabHomeCell = ActiveWorkbook.Sheets("Outputed Data").Cells(2, 2)
'set our value range
ActiveWorkbook.Sheets("Test Run 1").Cells(2, 17).Select
Range(Selection, Selection.End(xlDown)).Select
Set rngLTFT = Selection
'loop though our value range
For Each c In rngLTFT
If c.Value <> 0 Then 'ignore zero entries
arow = getrow(c.Offset(0, -9).Value) 'function to select MAP range
acol = getcol(c.Offset(0, -15).Value) 'function to select RPM range
arrVals(arow, acol) = arrVals(arow, acol) + c.Value 'add value to value array slot
arrCount(arow, acol) = arrCount(arow, acol) + 1 'increment counter for array slot
End If
Next
'loop though our output table and arrays
For c = 1 To 20
For r = 1 To 19
If arrCount(r, c) <> 0 Then
OutTabHomeCell.Offset(r, c).Value = arrVals(r, c) / arrCount(r, c)
End If
Next
Next
End Sub

Function getrow(mapval As Single) As Integer
If mapval <= 15 Then
getrow = 1
Else
For i = 2 To 19
If mapval <= (15 + (5 * (i - 1))) And mapval > (15 + (5 * (i - 2))) Then
getrow = i
Exit For
End If
Next
End If
End Function
Function getcol(rpmval As Single) As Integer
For i = 1 To 20
If rpmval <= (400 * i) And rpmval > (400 * (i - 1)) Then
getcol = i
Exit For
End If
Next
End Function

[black02ss]

Seems to work great... :) Now, just need to find out a way so I can understand how you got that and if it uses all the cells or if you have it delete some that show 0. Also, is there a way that I could talk to you on a instant messenger program or something??

I somewhat figured out the theory behind the code. Still need some help with the last two parts.



Function getrow(mapval As Single) As Integer
If mapval <= 15 Then
getrow = 1
Else
For i = 2 To 19
If mapval <= (15 + (5 * (i - 1))) And mapval > (15 + (5 * (i - 2))) Then
getrow = i
Exit For
End If
Next
End If
End Function
Function getcol(rpmval As Single) As Integer
For i = 1 To 20
If rpmval <= (400 * i) And rpmval > (400 * (i - 1)) Then
getcol = i
Exit For
End If
Next
End Function

Also, was wondering if there is anyway that if my LTFT for bank one is in a different column, it will search all columns and find it according to header name? Reason being, not all the time will it be in the same column.

Thanks again!!!!

[black02ss]

Ok, I have another question. What part of the above code do I need to change to alter the way it groups MAP and RPM values?? Previously I stated I need it to average them like 0-15, 15-20, ect... I realize that won't work. I need it to plot them like this. In cell for MAP 15 the average would be 13-17 instead of 0-15. Same thing applies to the RPM's. In cell RPM 400, I need the average to be 200-600 instead of 0-400.. In cell 800 you would have an average of 600-1000 instead of 400-800.


Thanks again guys!! This really helps me a bunch. If I can figure out this grouping and how to call by header name I will be set.

[Killian]

Hi Chad,
I've made some changes and put them in the attached workbook
It seems to work OK from what I can tell...
I put lots of comments in the code so hopefully you can understand what's going on. I think you get most of it but if you haven't done much programming, the iterative loops (especially in those final two functions) aren't very intuative but once you get where I'm coming from in principle, it should start to make sense, so here's a breakdown of the whole thing:

At the top, I've declared some string constants (the headings) so if these change on the worksheet you'll need to update these
The range variables are dimensioned up here so all the routines can use them.
The Sub "Main" just calls the routines in order, Init, AnalyzeData and CleanUp.
Init: initializes the ranges we need to work with and searches for the headings.
AnalyzeData: sets up two arrays (for value totals and value counts) for each position in the final table then loops thru the LTFT range and adds its value to the correct array position and increments a corresponding counter in the other array. When its done, for each position in the table, the result is calculated from the arrays (value/count).
So how do we get the correct array position? That's what the functions at the bottom do... they are passed a MAP/RPM value and they loop thru the value ranges you described and pass back which one they fall into (effectively the row and column of the final table)
CleanUp: just frees up the references we set

This should do the job. You can PM me here if I'm logged in or just let me know if you need any more of an explanation

Good luck
K :-)

PS: I just had to delete a few (thousand) rows of data to get the file size down for the upload... so you might want to add that back before you run it

[black02ss]

Thanks a lot man!!! I am going to look over this and take it all in. This is just awsome!!