Solved sort, average and hide rows of data

November 3, 2015 at 06:53:51
Specs: Macintosh
I have 24 hrs of data in seconds across 49 columns. I need to average the seconds to minutes and then hide the seconds. It would be preferable to remove the seconds once the minutes are averaged if possible

See More: sort, average and hide rows of data

Report •


✔ Best Answer
November 4, 2015 at 17:03:05
If you would rather do the Averaging on a second sheet, you can.

Your sheets should be named Sheet1 & Sheet2

Sheet1, all your data across 49 Columns and should look like:

 
     A      B       C       D       E       F           AV      AW       AX
 1) Time Label_1 Label_2 Label_3 Label_4 Label_5 <> Label_47 Label_48 Label_49
 2) 0    21.384  21.081  21.716  21.558  21.855     21.716   21.558   21.855   
 3) 1    21.372  21.056  21.722  21.545  21.83      21.722   21.545   21.83    
 4) 2    21.374  21.048  21.702  21.55   21.864     21.702   21.55    21.864   
 5) 3    21.359  21.062  21.706  21.523  21.84      21.706   21.523   21.84    
 6) 4    21.371  21.052  21.693  21.561  21.861     21.693   21.561   21.861   
 7) 5    21.359  21.043  21.706  21.533  21.852     21.706   21.533   21.852   
 8) 6    21.374  21.056  21.716  21.548  21.855     21.716   21.548   21.855   
 9) 7    21.368  21.042  21.718  21.544  21.851     21.718   21.544   21.851   
10) 8    21.371  21.046  21.709  21.544  21.851     21.709   21.544   21.851   

Sheet2 will look like:

 
    A       B       C       D       E       F         AV      AW       AX
 1) Sheet1       Label_1 Label_2 Label_3 Label_4 <> Label_47 Label_48 Label_49   
 2)   2     61           
 3)  62    121          
 4) 122    181          
 5) 182    241          
 6) 242    301          
 7) 302    361          
 8) 362    421          
 9) 422    481          
10) 482    541          

The word Sheet1, must go in cell A1

Put this new formula in Sheet2 Cell C2:

=AVERAGE(INDIRECT($A$1&"!"&SUBSTITUTE(ADDRESS(1,COLUMN()-1,4),"1","")&$A2&":"&SUBSTITUTE(ADDRESS(1,COLUMN()-1,4),"1","")&$B2))

It is a bit long, so you might want to just Copy & Paste.

Then drag across to Column Ax then drag it down.

See how that works for you.

MIKE

http://www.skeptic.com/



#1
November 3, 2015 at 08:07:10
What program are you using?

Is this an Excel spread sheet or some other Mac specific program.

MIKE

http://www.skeptic.com/


Report •

#2
November 3, 2015 at 08:34:02
I'm using Excel for Mac.
I have the file as a .csv and .xlxs

Report •

#3
November 3, 2015 at 08:51:04
I need to average the seconds to minutes and then hide the seconds.

What do you mean by "average the seconds to minutes"?
Do you mean to Truncate, or Round up or Round down to the next full minute
or something else?

What does your data look like?

Post a small sample, but first read the HOW-TO which explains the use
of the < PRE > tags.

http://www.computing.net/howtos/sho...

Please understand that I can not see your spread sheet from where I'm sitting
and I have no idea what it is your trying to do,
so you must explain everything in detail.

Also, for future reference, Excel question are best asked in the Office Software forum.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
November 3, 2015 at 15:51:13
So here is 2 minutes of data or 120 seconds, I would like to take the first 60 seconds, total them and divide by 60 to get an average unit of measure in minutes.
Thank you for the info on Office Software Forum.

Data:

TimeTc 1 T1U Tc 2 T1L Tc 3 T2U Tc 4 T2L Tc 5 T3U
0 21.384 21.081 21.716 21.558 21.855
1 21.372 21.056 21.722 21.545 21.83
2 21.374 21.048 21.702 21.55 21.864
3 21.359 21.062 21.706 21.523 21.84
4 21.371 21.052 21.693 21.561 21.861
5 21.359 21.043 21.706 21.533 21.852
6 21.374 21.056 21.716 21.548 21.855
7 21.368 21.042 21.718 21.544 21.851
8 21.371 21.046 21.709 21.544 21.851
9 21.383 21.048 21.721 21.531 21.845
10 21.369 21.041 21.723 21.539 21.834
11 21.364 21.074 21.715 21.547 21.835
12 21.382 21.057 21.698 21.543 21.831
13 21.395 21.063 21.714 21.553 21.834
14 21.395 21.067 21.73 21.553 21.853
15 21.402 21.067 21.715 21.569 21.851
16 21.392 21.073 21.714 21.559 21.841
17 21.399 21.052 21.715 21.579 21.838
18 21.383 21.045 21.702 21.554 21.848
19 21.397 21.053 21.719 21.555 21.846
20 21.394 21.056 21.69 21.558 21.849
21 21.397 21.047 21.707 21.539 21.837
22 21.391 21.037 21.707 21.536 21.85
23 21.385 21.037 21.726 21.568 21.856
24 21.396 21.049 21.712 21.56 21.858
25 21.387 21.033 21.699 21.557 21.848
26 21.382 21.047 21.717 21.555 21.85
27 21.388 21.037 21.729 21.561 21.84
28 21.395 21.044 21.732 21.559 21.85
29 21.384 21.027 21.738 21.57 21.849
30 21.386 21.055 21.731 21.56 21.854
31 21.388 21.021 21.736 21.552 21.862
32 21.357 21.028 21.714 21.543 21.85
33 21.382 21.025 21.732 21.571 21.85
34 21.387 21.021 21.716 21.551 21.852
35 21.376 21.045 21.724 21.547 21.847
36 21.384 21.027 21.712 21.554 21.839
37 21.383 21.042 21.702 21.547 21.845
38 21.383 21.042 21.731 21.557 21.838
39 21.387 21.03 21.719 21.535 21.846
40 21.366 21.028 21.714 21.543 21.825
41 21.382 21.057 21.707 21.555 21.843
42 21.377 21.026 21.712 21.554 21.832
43 21.381 21.037 21.709 21.554 21.839
44 21.372 21.034 21.717 21.552 21.843
45 21.372 21.034 21.71 21.552 21.853
46 21.366 21.025 21.707 21.546 21.847
47 21.373 21.058 21.705 21.534 21.851
48 21.384 21.03 21.725 21.551 21.842
49 21.377 21.039 21.724 21.535 21.842
50 21.365 21.043 21.69 21.564 21.852
51 21.377 21.046 21.683 21.564 21.836
52 21.379 21.041 21.694 21.536 21.84
53 21.385 21.044 21.704 21.559 21.843
54 21.373 21.051 21.717 21.572 21.85
55 21.375 21.05 21.703 21.558 21.859
56 21.391 21.043 21.716 21.568 21.843
57 21.402 21.052 21.715 21.576 21.842
58 21.388 21.053 21.71 21.568 21.833
59 21.38 21.042 21.696 21.56 21.845
60 21.389 21.054 21.727 21.56 21.841
61 21.391 21.063 21.726 21.558 21.83
62 21.397 21.046 21.713 21.574 21.839
63 21.4 21.059 21.706 21.564 21.836
64 21.407 21.05 21.703 21.571 21.849
65 21.403 21.046 21.709 21.58 21.833
66 21.404 21.038 21.698 21.568 21.837
67 21.384 21.039 21.674 21.56 21.823
68 21.387 21.017 21.696 21.563 21.839
69 21.384 21.014 21.693 21.545 21.833
70 21.392 21.013 21.695 21.553 21.828
71 21.398 21.002 21.688 21.54 21.828
72 21.395 20.997 21.698 21.55 21.831
73 21.402 21.025 21.688 21.546 21.831
74 21.405 21.022 21.682 21.54 21.834
75 21.381 21.002 21.69 21.539 21.836
76 21.383 21.01 21.683 21.557 21.832
77 21.384 21.02 21.68 21.57 21.823
78 21.4 21.017 21.693 21.551 21.836
79 21.388 21.022 21.701 21.571 21.837
80 21.396 21.013 21.686 21.551 21.835
81 21.398 21.025 21.679 21.553 21.834
82 21.38 21.026 21.679 21.547 21.851
83 21.396 21.013 21.695 21.55 21.851
84 21.392 21.013 21.682 21.537 21.834
85 21.392 21.006 21.698 21.553 21.831
86 21.4 20.998 21.706 21.555 21.83
87 21.394 21.005 21.703 21.571 21.827
88 21.401 21.012 21.716 21.561 21.827
89 21.388 20.992 21.694 21.574 21.839
90 21.389 21.013 21.679 21.553 21.845
91 21.367 21.013 21.699 21.544 21.839
92 21.385 21.015 21.694 21.549 21.824
93 21.387 21.008 21.68 21.545 21.836
94 21.391 20.999 21.684 21.558 21.821
95 21.394 20.995 21.684 21.539 21.839
96 21.402 20.997 21.699 21.557 21.854
97 21.387 20.982 21.677 21.551 21.829
98 21.397 20.982 21.703 21.564 21.826
99 21.391 20.983 21.681 21.571 21.824
100 21.396 20.988 21.699 21.541 21.835
101 21.381 20.992 21.687 21.551 21.82
102 21.379 20.997 21.685 21.54 21.838
103 21.379 21.013 21.673 21.547 21.834
104 21.386 21.009 21.689 21.54 21.841
105 21.388 20.999 21.697 21.546 21.821
106 21.394 21.002 21.697 21.564 21.824
107 21.384 20.992 21.69 21.564 21.83
108 21.381 20.995 21.7 21.548 21.827
109 21.368 20.983 21.7 21.545 21.817
110 21.39 20.994 21.702 21.554 21.829
111 21.388 21.015 21.706 21.555 21.82
112 21.398 21.003 21.685 21.565 21.831
113 21.408 21.019 21.691 21.546 21.831
114 21.394 21.021 21.703 21.564 21.836
115 21.404 21.012 21.704 21.562 21.837
116 21.394 21.024 21.697 21.548 21.843
117 21.374 21.017 21.686 21.544 21.845
118 21.375 21.034 21.694 21.555 21.82
119 21.374 21.033 21.693 21.551 21.83
120 21.367 21.026 21.708 21.547 21.829/pre>


Report •

#5
November 3, 2015 at 16:56:07
OK, we have the data, but the numbers are meaningless to me.

Row 0 shows: ( There is no row zero in Excel )

      A      B      C      D      E
0)  21.384 21.081 21.716 21.558 21.855

Are these numbers Seconds? IE: 21.384 seconds?

I would like to take the first 60 seconds,

Where are the first sixty seconds?
The first 3 numbers in the first row?
The first 3 numbers in the first three rows?

Remember, I have NO IDEA what your doing,
you need to explain everything in detail.

MIKE

http://www.skeptic.com/


Report •

#6
November 3, 2015 at 18:15:47
Please click on the following line and read the instructions on how to post example data in this forum so that columns remain lined up.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
November 3, 2015 at 18:22:07
I would like to take the first 60 seconds,

Assuming that the first 60 Rows of Column B, from 0 to 59
is what you want to Average, then simply using the AVERAGE()
function should give you what your looking for.

So for Column B, Rows 1 thru 60

     A    B
1 )  0  21.384
2 )  1  21.372
3 )  2  21.374
4 )  3  21.359
5 )  4  21.371
6 )  5  21.359
7 )  6  21.374
8 )  7  21.368
9 )  8  21.371
10)  9  21.383
11) 10  21.369
12) 11  21.364
13) 12  21.382
14) 13  21.395
15) 14  21.395
16) 15  21.402
17) 16  21.392
18) 17  21.399
19) 18  21.383
20) 19  21.397
21) 20  21.394
22) 21  21.397
23) 22  21.391
24) 23  21.385 
25) 24  21.396
26) 25  21.387
27) 26  21.382
28) 27  21.388
29) 28  21.395
30) 29  21.384
31) 30  21.386
32) 31  21.388
33) 32  21.357
34) 33  21.382
35) 34  21.387
36) 35  21.376
37) 36  21.384
38) 37  21.383
39) 38  21.383
40) 39  21.387
41) 40  21.366
42) 41  21.382
43) 42  21.377
44) 43  21.381
45) 44  21.372
46) 45  21.372
47) 46  21.366
48) 47  21.373
49) 48  21.384
50) 49  21.377
51) 50  21.365
52) 51  21.377
53) 52  21.379
54) 53  21.385
55) 54  21.373
56) 55  21.375
57) 56  21.391
58) 57  21.402
59) 58  21.388
60) 59  21.38

The formula =AVERAGE(B1:B60)
should give you an Average of 21.38

Is that what your trying to do?

MIKE

http://www.skeptic.com/


Report •

#8
November 4, 2015 at 06:53:40
My apologies, column A is titled Time (seconds)
Row 1 is 0 which is the start of the test, row 2 is 1 second and so on.
the data, under headings like TC1U is a thermocouple that is capturing 21.397 that is temperature in centigrade. My request is to take 60 seconds of temperatures, total them and divide by 60 to get the average temperature for 60 seconds or 1 minute. 1 minute data is what I am trying to get to. The data in seconds is cumbersome, not necessary to our test results and too large of files to work with.
Further down the columns are other TC temps and Current Transducers (CT's) capturing amperage that can be averaged as well using the same process.
Does this help?
Mike

Report •

#9
November 4, 2015 at 07:32:00
Still not sure what figures you want Averaged.

You have 5 columns of data.

Do you want the Columns Averaged individually
So it would be just Column B from Row 1 thru Row 60 ( Time 0 - 59 )

IE: =AVERAGE( B1 : B60 )

Then Column C from Row 1 thru Row 60
Then Column D from Row 1 thru Row 60, ETC, ETC.

Or, do you want the Columns Averaged as a matrix
So it would be Column B thru Column F, Row 1 thru 60 (Time 0 - 59 )

IE: =AVERAGE( B1 : F60 )

Or am I completely missing the idea?

MIKE

http://www.skeptic.com/


Report •

#10
November 4, 2015 at 07:32:25
Is there a reason that you have not honored our requests to format your data using the < pre > tags?

By following the instructions found via the link at the bottom of this post, you will be able to format your example data in a manner that will allow all of us to refer to specific columns and rows. That will eliminate a lot of the confusion. That will also allow us to copy the data directly into a spreadsheet and know that we are looking at the same layout as you.

Once that is done, instead if telling us that you want "to take 60 seconds of temperatures, total them and divide by 60" you can say (for example) "I want to average the values in B2:C60".

A request like that is a lot more specific than "take 60 seconds of temperatures, total them and divide by 60"

Please click on the following line, read the instructions and then repost your example data as requested.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
November 4, 2015 at 07:40:05
DerbyDad03,

I believe the OP did try to use the < PRE > tags in reply #4
if you look at the last row of data you can see where he missed
putting in the Left Arrow.

There is still going to be a bit of confusion, because he starts his
list of measurements with line number Zero.
So 60 seconds for his list is 0 thru 59.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#12
November 4, 2015 at 08:00:53
Yes your first example of Average(B1:B59)
I am starting with line 1 but the time is 0 at line 1 If that or 0-59 is a problem we can delete the 0 start and go 1-60 if that makes more sense?
I'm sorry if I missed something on the insert.

Report •

#13
November 4, 2015 at 08:12:47
re: "I believe the OP did try to use the < PRE > tags in reply #4
if you look at the last row of data you can see where he missed
putting in the Left Arrow.
"

Thus the reason the How To includes the line:

Repeat steps 4 – 6 as often as necessary until you like 
the way the post looks and then click Confirm.

When the post was previewed or even posted, it should have been edited until it was formatted correctly. I realize that the editor in this forum isn't the most convenient, but it's all we have. As you are well aware, using the < pre > correctly really helps to avoid the exact type of confusion we are experiencing here.

re:" There is still going to be a bit of confusion, because he starts his
list of measurements with line number Zero.

Thus the reason the How-To includes an example that shows Column letters and Row numbers. I know that you ask for them all the time. Even if mikececchini did attempt to use the < pre > tags, he didn't use Column letters or Row numbers as instructed.

I think that if he reposts the data in the format that we've requested, we will be able to move forward with some solid suggestions. I know that you are trying to help and we all appreciate that. Unfortunately, we still guessing at his requirements because of the
< pre > tag issue.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
November 4, 2015 at 09:38:54
OK, so we have the function you need, =AVERAGE()

Now, you have 24 Hours of measurements, split into 1 Second increments,
which means you have approximately 86400 lines of data.

You have 5 columns of data so you have approximately 432000 cell with data.

You want averages every 60 Seconds, which means you will have approximately 1440 lines of Averages for 5 columns.

Is there any special way you need to display the averages.

Do you need to compare across Columns?

What would be the best way?


MIKE

http://www.skeptic.com/


Report •

#15
November 4, 2015 at 10:35:18
Try this and see how it works for you.

With your data in Excel like this:

     A      B       C       D       E       F       G   
 1) Time Label_1 Label_2 Label_3 Label_4 Label_5        
 2) 0    21.384  21.081  21.716  21.558  21.855         
 3) 1    21.372  21.056  21.722  21.545  21.83          
 4) 2    21.374  21.048  21.702  21.55   21.864         
 5) 3    21.359  21.062  21.706  21.523  21.84          
 6) 4    21.371  21.052  21.693  21.561  21.861         
 7) 5    21.359  21.043  21.706  21.533  21.852         
 8) 6    21.374  21.056  21.716  21.548  21.855         
 9) 7    21.368  21.042  21.718  21.544  21.851         
10) 8    21.371  21.046  21.709  21.544  21.851         

Starting at Column H add this data:

     H       I        J       K       L       M       N  
 1) R_S     R_E     Label_1 Label_2 Label_3 Label_4 Label_5
 2) 2       61              
 3) 62      121             
 4) 122     181             
 5) 182     241             
 6) 242     301             
 7) 302     361             
 8) 362     421             
 9) 422     481             
10) 482     541             

Column H is the Row Start Number
Column I is the Row End Number

We will be using those numbers in the =AVERAGE() formula.

You will need to continue to increment the Row Start & End numbers down to row number 1440.

The quickest way is to select Column H & I, Row 9 & 10
and grab the Auto Fill button, the small square on the
lower right corner of the box, and drag down.
If you've done it correctly, all the numbers should automatically increment properly.

Once you have all the Row Start & Row End numbers,
in Cell J2 enter the formula:

=AVERAGE(INDIRECT("B"&$H2&":"&"B"&$I2))

in cell K2: =AVERAGE(INDIRECT("C"&$H2&":"&"C"&$I2))
in cell L2: =AVERAGE(INDIRECT("D"&$H2&":"&"D"&$I2))
in cell M2: =AVERAGE(INDIRECT("E"&$H2&":"&"E"&$I2))
in cell N2: =AVERAGE(INDIRECT("F"&$H2&":"&"F"&$I2))

Once you have all the formula entered,
select cells J2 thru N2
and again, using the Auto Fill button, the small square on the lower right corner of the box, and drag down.

Your data should now look something like:

      H       I        J       K       L       M       N  
  1) R_S     R_E     Label_1 Label_2 Label_3 Label_4 Label_5
  2) 2       61     21.3811  21.0451 21.7131 21.5528 21.8457    
  3) 62      121    21.3906  21.0131 21.6939 21.5545 21.8330    
  4) 122     181    21.1605  20.8195 22.1505 21.429  21.7995    
  5) 182     241    20.7405  20.3995 23.0505 21.189  21.7395    
  6) 242     301    20.3205  19.9795 23.9505 20.949  21.6795    
  7) 302     361    19.9005  19.5595 24.8505 20.709  21.6195    
  8) 362     421    19.4805  19.1395 25.7505 20.469  21.5595    
  9) 422     481    19.0605  18.7195 26.6505 20.229  21.4995    
 10) 482     541    18.6405  18.2995 27.5505 19.989  21.4395    

MIKE

http://www.skeptic.com/


Report •

#16
November 4, 2015 at 15:48:23
Thank you for all of your help. I actually have 49 columns of data. I truncated it to fit into the chat window, but we are on the right track. I will start with your suggestions above and go from there. Stand by!
Mike

Report •

#17
November 4, 2015 at 17:03:05
✔ Best Answer
If you would rather do the Averaging on a second sheet, you can.

Your sheets should be named Sheet1 & Sheet2

Sheet1, all your data across 49 Columns and should look like:

 
     A      B       C       D       E       F           AV      AW       AX
 1) Time Label_1 Label_2 Label_3 Label_4 Label_5 <> Label_47 Label_48 Label_49
 2) 0    21.384  21.081  21.716  21.558  21.855     21.716   21.558   21.855   
 3) 1    21.372  21.056  21.722  21.545  21.83      21.722   21.545   21.83    
 4) 2    21.374  21.048  21.702  21.55   21.864     21.702   21.55    21.864   
 5) 3    21.359  21.062  21.706  21.523  21.84      21.706   21.523   21.84    
 6) 4    21.371  21.052  21.693  21.561  21.861     21.693   21.561   21.861   
 7) 5    21.359  21.043  21.706  21.533  21.852     21.706   21.533   21.852   
 8) 6    21.374  21.056  21.716  21.548  21.855     21.716   21.548   21.855   
 9) 7    21.368  21.042  21.718  21.544  21.851     21.718   21.544   21.851   
10) 8    21.371  21.046  21.709  21.544  21.851     21.709   21.544   21.851   

Sheet2 will look like:

 
    A       B       C       D       E       F         AV      AW       AX
 1) Sheet1       Label_1 Label_2 Label_3 Label_4 <> Label_47 Label_48 Label_49   
 2)   2     61           
 3)  62    121          
 4) 122    181          
 5) 182    241          
 6) 242    301          
 7) 302    361          
 8) 362    421          
 9) 422    481          
10) 482    541          

The word Sheet1, must go in cell A1

Put this new formula in Sheet2 Cell C2:

=AVERAGE(INDIRECT($A$1&"!"&SUBSTITUTE(ADDRESS(1,COLUMN()-1,4),"1","")&$A2&":"&SUBSTITUTE(ADDRESS(1,COLUMN()-1,4),"1","")&$B2))

It is a bit long, so you might want to just Copy & Paste.

Then drag across to Column Ax then drag it down.

See how that works for you.

MIKE

http://www.skeptic.com/


Report •

#18
November 5, 2015 at 07:08:18
Great Thank you for all your help!
Mike

Report •


Ask Question