#1
|
|||
|
|||
Formatting data in scattered rows into rows following each other
Hi All,
I have been searching the net and this forum to see how to do that, but I have been unable to find a way to do that. I do noise studies, and typically I have data in a number of columns in an excel spreadsheet (below example of maximum noise levels recorded during a 10-minute measurement). 2016/12/07 13:45 92.4 2016/12/07 13:55 90 2016/12/07 14:05 92.6 2016/12/07 14:15 91.6 2016/12/07 14:25 94 2016/12/07 14:35 97.7 2016/12/07 14:45 95.1 2016/12/07 14:55 96.3 2016/12/07 15:05 92.7 2016/12/07 15:15 93.8 2016/12/07 15:25 86.8 2016/12/07 15:35 88 2016/12/07 15:45 87.6 I need to calculate the equivalent value over an hour period (a logarithmic calculation ) which is easy. The problem is that the value is reported in every 6th row. If I have a few hours of data it is not a problem as I just copy and paste, but I have three projects where I have a few weeks worth of data and this is not possible for me to do this manually. Can anyone propose a way that I can do this to allow Excel to either calculate it in following rows, or so that I can do the calculation and by running a macro or a VBA programme I can get it in following rows (something like this). 2016/12/07 13:45 94.7 2016/12/07 14:45 99.3 2016/12/07 15:45 97.1 Thanks |
#2
|
||||
|
||||
Could you please explain how the three last values are obtained?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Those are just numbers I typed but the calculation is something along the line of ...
Imagine Column A is a filled with the measurement number (e.g. 1, 2, 3, 4, 5, 6, 7 etc.), with column B containing the numbers used to calculate the equivalent number, the calculation is something like for the first 3 values: Code:
A B C 1 2 84.9 3 57 4 30 80.1 Code:
x = (10 * log((1/(A4 - A1)) * (10^(B2/10)+10^(C2/10)+10^(D2/10)))) X = 80.1 Code:
x = (10 * log((1/(A7 - A1)) * (10^(B2/10)+10^(C2/10)+10^(D2/10)+10^(DE/10)+10^(F2/10)+10^(G2/10)))) |
#4
|
||||
|
||||
So you are trying to extract the 1st, 7th, 13th... values from a list?
Assuming you start in row1 col D ( or column to your choice) and your data starts in A1 try=INDEX(A$1:A$13,6*ROW()-5) Drag right for three columns, and down as far as needed Adapt the range to your needs but keep the relative reference in mind
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Embedded Excel charts refresh only data values, not which data points (rows) are included | GregL | Word | 0 | 12-01-2016 10:24 AM |
Delete blank rows between the two rows that contain data | beginner | Excel Programming | 5 | 12-26-2014 12:29 AM |
Delete All empty Rows - Print - Undo all Rows deleted | Bathroth | Word VBA | 1 | 10-01-2014 01:40 PM |
Grouping table rows to prevent individual rows from breaking across pages | dennist77 | Word | 1 | 10-29-2013 11:39 PM |
Count rows and add blank rows accordingly | Hoochtheseal | Word VBA | 1 | 01-29-2013 09:23 PM |