Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-09-2016, 04:02 AM
MorneDJ MorneDJ is offline Formatting data in scattered rows into rows following each other Windows 7 64bit Formatting data in scattered rows into rows following each other Office 2007
Novice
Formatting data in scattered rows into rows following each other
 
Join Date: Dec 2016
Posts: 2
MorneDJ is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 12-09-2016, 07:18 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Formatting data in scattered rows into rows following each other Windows 7 64bit Formatting data in scattered rows into rows following each other Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #3  
Old 12-09-2016, 07:36 AM
MorneDJ MorneDJ is offline Formatting data in scattered rows into rows following each other Windows 7 64bit Formatting data in scattered rows into rows following each other Office 2007
Novice
Formatting data in scattered rows into rows following each other
 
Join Date: Dec 2016
Posts: 2
MorneDJ is on a distinguished road
Default

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
For 6 rows it will be:


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))))
Reply With Quote
  #4  
Old 12-10-2016, 12:35 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Formatting data in scattered rows into rows following each other Windows 7 64bit Formatting data in scattered rows into rows following each other Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


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
Formatting data in scattered rows into rows following each other Delete blank rows between the two rows that contain data beginner Excel Programming 5 12-26-2014 12:29 AM
Formatting data in scattered rows into rows following each other Delete All empty Rows - Print - Undo all Rows deleted Bathroth Word VBA 1 10-01-2014 01:40 PM
Formatting data in scattered rows into rows following each other Grouping table rows to prevent individual rows from breaking across pages dennist77 Word 1 10-29-2013 11:39 PM
Formatting data in scattered rows into rows following each other Count rows and add blank rows accordingly Hoochtheseal Word VBA 1 01-29-2013 09:23 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:58 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft