Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations cowski on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to add rows and increment averaged amount between them

Status
Not open for further replies.

dagur777

Chemical
Nov 11, 2001
18
How do you write a program or VB to increase the rows?
Also, any easy way to increment the average amount
between them? I have attached the sample excel file.
The actually data has about 1000 rows.

Original Data Set (5 minutes Snap Shot)

COEFF A COEFF B TIMESTAMP
0.779705644 3.485488892 6/18/2001 0:05
0.778691053 3.4968822 6/18/2001 0:10
0.779860318 3.496875763 6/18/2001 0:15
0.78243196 3.509756804 6/18/2001 0:20

Final Data Set (would like to change to 1 minute snap shot)
COEFF A COEFF B TIMESTAMP
0.779705644 3.485488892 6/18/2001 0:05
0.779502726 3.487767553 6/18/2001 0:06
0.779300000 3.490046215 6/18/2001 0:07
0.779096889 3.492324877 6/18/2001 0:08
0.778893971 3.494603539 6/18/2001 0:09
0.778691053 3.496882200 6/18/2001 0:10
0.778924906 3.496880913 6/18/2001 0:11
0.779158759 3.496880000 6/18/2001 0:12
0.779392612 3.496878338 6/18/2001 0:13
0.779626465 3.496877050 6/18/2001 0:14
0.779860000 3.496875763 6/18/2001 0:15



0.78243196 3.509756804 6/18/2001 0:20
etc.

DaGur
 
Replies continue below

Recommended for you

I have NO idear what you are trying to do?????

Do you want to group the data (eg how many "A values" from 0.779-0.78 etc.)?

Increase what rows? there are a infinite (or close to but at much more than 1000 rows) in an Excel sheet

Best Regards

Morten
 
He wants a VB program to insert (in this case) 4 rows between each existing row and then take the timestamp and split it evenly through the newly inserted rows.

Sorry, but I would have to rely on insert the first four rows manually, and then copy that new format though the sheet.

Possibly you can record a macro of the first couple of steps, save it, then look at the programming to get you started.


Remember...
"If you don't use your head, your going to have to use your feet."
 
The macro recorder is probably the single most useful tool that you have at your disposal.

If you record the manual steps involved in your operation, most of the time, the remaining work is to simply figure out how to structure the loop to automate the operation and how to generate the relative cell addressing needed.

TTFN
 
btw... is the data actually linear? you might want some sort of spline fit and then increment on the spline.

TTFN
 
If I understand you correctly, you want to add new readings to an already existing table and then you want to ensure that the new table is "sorted" according to the times of the readings. You then want to obtain averages.

Is this correct?

If so, the easiest way to accomplish it is to instert the new readings at the end of the table you already have, and then use the built-in sorting tool.

Depending on your formatting needs, the best way to ensure that all items are included in any averaging is to use the entire column, and place the result on a separate page, or if you must have the average on the same page, you may consider placing the answer at the top of the table, rather than the bottom (as this location won't change), and use a formula to cover the largest possible number of rows you'll ever have. As another option, you can use VBA to do the averaging.

If you give me more specific information, I can probably give you more specific ways of doing what you need.



 
how about if you make a second worksheet that uses formulas based on the first sheet to accomplish your goal?

one possible method:

Create 5 lines of sheet2 as follows:
- first column contains row numbers
- first row uses the index function to copy a row (one cell at a time) from the other sheet. ex: =INDEX(Sheet1!B3:B12,INT(A3/5)+1,0)
- next four rows contain the required functions to interpolate linearly between the 6th row and the 1st (they will not display the right info until later)

Then "copy down" your formulas to fill the sheet. Your interp'd rows should now show the right info.








 
Status
Not open for further replies.

Part and Inventory Search

Sponsor