Excel: Summary 3 Data Sets

[GGleason]

I have 3 sets of data. Each data set has 2 columns, Time and Thrust. I want to sum the results, but the times are different for each. Here is sample data (comma delimited):

Time, Thrust (N), Time, Thrust (N), Time, Thrust (N)
0, 0, 0, 0, 0, 0
0.016, 4.086, 0.009, 2.078, 0.027, 0.975
0.023, 11.44, 0.011, 3.896, 0.081, 4.777
0.035, 14.546, 0.014, 14.286, 0.122, 8.577
0.058, 21.411, 0.023, 31.948, 0.162, 12.865
0.074, 21.739, 0.032, 32.468, 0.184, 14.035
0.09, 21.087, 0.038, 30.909, 0.198, 13.354
0.101, 21.742, 0.048, 30.909, 0.203, 11.309
0.125, 22.071, 0.079, 29.87, 0.217, 8.095
0.145, 22.072, 0.104, 30.13, 0.244, 6.148

What is the best strategy to get a new time and sum column so I can chart the aggregate thrust curve?

GGleason

 

[bluedragon2]

What is your criteria for summing?

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[GGleason]

I am not sure I understand your question, so I’ll clarify. This would not be an issue if the times were in synch with each other. I was thinking of having time increments of 0.05 or finding all time occurrences and summarizing at that time point (which for these 10 data points for these 3 sets reduces to 27, but could have been up to 30). Accuracy is a criterion, followed by simplicity. Let me know if I can clarify further.

GGleason

 

[GGleason]

Also, I am assuming I need a fourth data set, with time and summary thrust so I can produce the summary graph.

GGleason

 

[bluedragon2]

Still not sure what you are wanting as a result. What would you expect the results of your sample data would look like?

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[GGleason]

For example, a fourth data set would be added and have an initial row of 0,0. A second row would have a time of 0.02, and a thrust of 35.07 (linear interpolation, then addition of the 3 sets). The next row would be 0.04 for time and the next thrust result, etc.

GGleason

 

[SkipVought]

Just stack them up in 2 column, sort on Time.

Then I used MS query to
[tt]
Select Time, SUM(Thrust)
From [Sheet1$]
Group By Time
[/tt]
and plotted that.

From start to finish, took about 55 seconds.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[GGleason]

I have never heard of MS Query. It looks like you ran a SQL query on Excel data? Wow...

How do you invoke MS Query?

GGleason

 

[SkipVought]

faq68-5829

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[bluedragon2]

Skip,

I don't have MS Query installed here, but ran this in access. I got the following results with his data example:

[tt]
0 0
0.009 2.078
0.011 3.896
0.014 14.286
0.016 4.086
0.023 43.388
0.027 0.975
0.032 32.468
0.035 14.546
0.038 30.909
0.048 30.909
0.058 21.411
0.074 21.739
0.079 29.87
0.081 4.777
0.09 21.087
0.101 21.742
0.104 30.13
0.122 8.577
0.125 22.071
0.145 22.072
0.162 12.865
0.184 14.035
0.198 13.354
0.203 11.309
0.217 8.095
0.244 6.148
[/tt]

Are they correct?

Thanks

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

Nevermind, I see that I did something wrong...

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Access would work just find for the SUM.

However, I detest PivotCharts, and the ONLY chart you can get from a PivotTable is a PivotChart.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[SkipVought]

I'd go one step further, with the table of Time/Thrust values.

I would add a column for TestID, storing ALL the tests in one table.

You can choose which test(s) you want to summatize in MS Query using the Is One Of in the Add Criteria Opeator selection. This give you an IN list in the Where clause.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[GGleason]

Skip,

I figured it out. However, I do not believe the results I get, which are:

0.0, 0.0
0.009, 2.078
0.011, 3.896
0.014, 14.286
0.016, 4.086
0.023, 43.388
0.027, 0.975
0.032, 32.468
0.035, 14.546
0.038, 30.909
0.048, 30.909
0.058, 21.411
0.074, 21.739
0.079, 29.87
0.081, 4.777
0.09, 21.087
0.101, 21.742
0.104, 30.13
0.122, 8.577
0.125, 22.071
0.145, 22.072
0.162, 12.865
0.184, 14.035
0.198, 13.354
0.203, 11.309
0.217, 8.095
0.244, 6.148

If you look between 0 and 0.032 seconds, the thrust data is going up and down. That is not what is going on in real life. The thrust should be increasing for all data points since.

GGleason

 

[SkipVought]

Believe it or not, that's the data that you posted.

It's not the process that's at fault, my friend!

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[SkipVought]

If you look carefully, there is only ONE time value that has multiple (2) data points that actually SUM. All other values are distinct thrust for time.

But maybe you don't really want to SUM. Rather you are accumulating data from multiple tests.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[GGleason]

I think the process is fine, just the SQL statement is the incorrect application for this situation.

The reality: the thrust is going up at all 3 data sets prior to t+0.032. Common sense tells me I should expect the data to be increasing at all points. The results of the SQL statement do not do that. Therefore the SQL statement is not correct and not suitable as a solution. If you think my conclusion is incorrect, tell me where my math or logic is flawed. If I do the arduous manual linear interpolation doing y-intercepts for a given time. I assure you I will come out with different values indicated by the query.

GGleason

 

[SkipVought]

Did you look at your raw data???

There are WAY DOWN points after the UP points along the time line, IN THE DATA YOU POSTED!!!!!!

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[SkipVought]

"Just stack them up in 2 column, sort on Time."

In fact, just plot those two columns WITHOUT doing any query. Your bubble is burst!!! What's wrong with your data? Or is it the way you are interpreting the data?

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[GGleason]

I did double check the posted data as suggested and the data is correct as well as my original conclusions. My advantage is that I had graphed the 3 sets of data beforehand so I was aware of the trends. If you graph the 3 thrust curves one can clearly see a rising trend for all 3 curves before t+0.032. Therefore if a new curve were created based on the combined 3 curves, it would be an increasing curve up to t+0.032. I am not sure Excel has a feature to aggregate sum like I need (or Access for that matter). I think that for this problem it may require a VBA solution. The solution will be to find all the unique time points. Then for a given point that has thrust data then interpolate the thrust for the other 2 times. You keep doing that until all time points have thrust data. The last step is to sum the thrust points at each time. That will yield the results that I am looking for. While not an elegant solution (since is brute force programming and not native Excel functionality) it should work. Thanks to all who have commented. If I find a better solution than the one outlined above, then I will post it.

GGleason