34blazer

Didn't want to hijack Ronny's thread...


So I have been trying to build a pivot table with Excel to help crunch some data. Little background; Helping a friend tune his Turbo Buick and tuning has been progressing slower than I'd like, there has been issues along the way but not having a history table to help dial in the fuel portion of the tune hinders progress. We are using the EBL SFI 6. The WUD contains somewhat of a history table, but that's for corrections mainly, and only up to 100kpa. In the past I've been spoiled by the history table that is contained in the S_AUJPV4 adx, and I added a WBO2 tracking table to help dial in the AFRs rather quickly. So far I have been able to construct a crude pivot table in excel using the data contained from the datalogs I received from my friend, it's shaping up nicely, but I have a long way to go. All of the tutorials I can find are lacking the details I need to finish up the table. Attached is a picture of the current progress, the actual file is too large to upload on the forum, but I could zip it if someone wanted to take a closer look.


Here's the details of the current table:


Row format is "RPM"
Column format is "KPA"
Field is "WBO2"
As you can see in the picture, the labels for each row/column are that of which was contained in the datalog. So for example, if there is no data contained at 260 kpa, then there is no header label for it. I'd like the KPA portion to extend up to 300 kpa, and the rpm to extend up to 6000 rpm, or so, but don't know how to manually add the labels.








Here's what I want to figure out:


How to manually create the headers for the Row/Columns to more closely match the VE table.


I.E. 40, 45, 50,55,60, etc for the KPA
and 2000, 2400, 2800, 3200, etc for the RPM


I can save it as a template but each time I open the file excel automatically refreshes it with the bin that was used to create the table, I don't want it to do that, just a blank template.


Once the table is built, I need to know how to link any new datalogs to automatically populate the table, I started to read about adding a macro but got confused, but it would be nice to click a button to automatically populate the table. Just don't know how to do that right now.




Help?

34blazer

Anyone wanna take a stab at it?

JP86SS

iTrader: (1)

OK, Throwing ideas out to crack this!
I see what you are trying to do and it's really not that easy (from my pea brain use of Excel anyway)
I do not see a way of setting breakpoints in a pivot table, looks like another theory is needed.

I don't think you can do this as one big calculation to figure out where to put the result. It would tie up the computer until it was ALL done.
A stepping through line by line will take longer but give you a way to break the operation if needed. Seems to be a better way.

Define what your table needs look like.
Create 2 separate tables (maybe 3 or 4 hidden too)
Steps by line:
Grab RPM (actual value from log)
Round by your table increment (This may be the tough part without extra steps to get right)
Determine if rounding was + or - (actual compared to rounded)
Now you know what Cell it should go into on the RPM scale.

This is where my thoughts fell apart.
But... I don't know how to define this or where to store it. (Thinking variable definition)

Store the RPM cell range variable.

Do the same type of thing for the Kpa value but using its increments.

Store the Kpa range variable variable.

Locate the correct cell in the matrix.

Store a "Cell result count" in the correct cell as a running sample count.
Add the fuel correction value into a duplicate cell (another table) as running total.
Once the routine is completed running all lines of data, have the calculation of running average do its thing.
Or you could have a table that is always calculating the running average as the actual thing you are watching.

There seem to be other ways using Vlookups etc but I think they will need to analyze everything for each line and would not be as easy to confirm the results.

Gotta run, Something to begin with anyway.
Jp

34blazer

Thanks JP


I would like to model this after the history table found in the S_AUJP .adx, only change would be for the table to be expanded to 300kpa. Correct me if I'm wrong, but isn't the history tables found in TP modeled directly from Excel?

JP86SS

iTrader: (1)

I do not know what Magnus uses as the background.
I would think something open source rather than the MS due to cost issues.
If someone can assist with storing of the variables and lookups of the correct cells, that may help this along.

32V_DOHC

The way I would do this is:

Start with a clean sheet. On the first tab you will copy in data. You can use macros for this but copying the whole sheet is pretty fast. On the second tab start at the top and add the headers in row 1. Then add a row for each cell of the table adding RPM, MAP and a 0 in the WB column. Then under those add a formula to copy data from sheet 1 to populate the rows with your data. Then add a column next to MAP. Make a formula to take MAP and divide by the kpa step between table columns (5 or 10 kpa) then round to a whole number. Then multiply by the kpa step. For step of 5 kpa 53 kpa is divided by 5 to get 10.6 which is rounded to 11 then multiplied by 5 to get 55 kpa. If your cal uses two different steps then use an if then to select the correct step. Do the same for RPM. Then do a pivot table. You should get a table of the right size. In each cell where you have data the number will be non-zero. For cells where you have no data the cell will be zero.

Now copy the second tab. In the rows that contained 0 for WB add a formula to WB to look at the coresponding cell from the pivot table. If the cell is zero then add a value that is nuetral for that cell (example 14.7). If the cell is not zero then add a space in quotes. This will remove that value from the average. Then make another pivot table from the data on the new tab. This one will have the right size and averages for data you collected and neutral data that won't change cells for the cell where you got no data.

HTH

John

34blazer

Thanks for the tips! Eyes are still a little foggy after the PRK surgery, once healed up enough Im going to resume building the table.


If I understand correctly, I will be essentially manually building a pivot table, then converting it into a pivot table?


What formulas will I need to use?