# 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

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 & Sheet2Sheet1, 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 A1Put 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.MIKEhttp://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.MIKEhttp://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 minuteor something else?What does your data look like?Post a small sample, but first read the HOW-TO which explains the useof the < PRE > tags.http://www.computing.net/howtos/sho...Please understand that I can not see your spread sheet from where I'm sittingand 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.MIKEhttp://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 T3U0 21.384 21.081 21.716 21.558 21.8551 21.372 21.056 21.722 21.545 21.832 21.374 21.048 21.702 21.55 21.8643 21.359 21.062 21.706 21.523 21.844 21.371 21.052 21.693 21.561 21.8615 21.359 21.043 21.706 21.533 21.8526 21.374 21.056 21.716 21.548 21.8557 21.368 21.042 21.718 21.544 21.8518 21.371 21.046 21.709 21.544 21.8519 21.383 21.048 21.721 21.531 21.84510 21.369 21.041 21.723 21.539 21.83411 21.364 21.074 21.715 21.547 21.83512 21.382 21.057 21.698 21.543 21.83113 21.395 21.063 21.714 21.553 21.83414 21.395 21.067 21.73 21.553 21.85315 21.402 21.067 21.715 21.569 21.85116 21.392 21.073 21.714 21.559 21.84117 21.399 21.052 21.715 21.579 21.83818 21.383 21.045 21.702 21.554 21.84819 21.397 21.053 21.719 21.555 21.84620 21.394 21.056 21.69 21.558 21.84921 21.397 21.047 21.707 21.539 21.83722 21.391 21.037 21.707 21.536 21.8523 21.385 21.037 21.726 21.568 21.85624 21.396 21.049 21.712 21.56 21.85825 21.387 21.033 21.699 21.557 21.84826 21.382 21.047 21.717 21.555 21.8527 21.388 21.037 21.729 21.561 21.8428 21.395 21.044 21.732 21.559 21.8529 21.384 21.027 21.738 21.57 21.84930 21.386 21.055 21.731 21.56 21.85431 21.388 21.021 21.736 21.552 21.86232 21.357 21.028 21.714 21.543 21.8533 21.382 21.025 21.732 21.571 21.8534 21.387 21.021 21.716 21.551 21.85235 21.376 21.045 21.724 21.547 21.84736 21.384 21.027 21.712 21.554 21.83937 21.383 21.042 21.702 21.547 21.84538 21.383 21.042 21.731 21.557 21.83839 21.387 21.03 21.719 21.535 21.84640 21.366 21.028 21.714 21.543 21.82541 21.382 21.057 21.707 21.555 21.84342 21.377 21.026 21.712 21.554 21.83243 21.381 21.037 21.709 21.554 21.83944 21.372 21.034 21.717 21.552 21.84345 21.372 21.034 21.71 21.552 21.85346 21.366 21.025 21.707 21.546 21.84747 21.373 21.058 21.705 21.534 21.85148 21.384 21.03 21.725 21.551 21.84249 21.377 21.039 21.724 21.535 21.84250 21.365 21.043 21.69 21.564 21.85251 21.377 21.046 21.683 21.564 21.83652 21.379 21.041 21.694 21.536 21.8453 21.385 21.044 21.704 21.559 21.84354 21.373 21.051 21.717 21.572 21.8555 21.375 21.05 21.703 21.558 21.85956 21.391 21.043 21.716 21.568 21.84357 21.402 21.052 21.715 21.576 21.84258 21.388 21.053 21.71 21.568 21.83359 21.38 21.042 21.696 21.56 21.84560 21.389 21.054 21.727 21.56 21.84161 21.391 21.063 21.726 21.558 21.8362 21.397 21.046 21.713 21.574 21.83963 21.4 21.059 21.706 21.564 21.83664 21.407 21.05 21.703 21.571 21.84965 21.403 21.046 21.709 21.58 21.83366 21.404 21.038 21.698 21.568 21.83767 21.384 21.039 21.674 21.56 21.82368 21.387 21.017 21.696 21.563 21.83969 21.384 21.014 21.693 21.545 21.83370 21.392 21.013 21.695 21.553 21.82871 21.398 21.002 21.688 21.54 21.82872 21.395 20.997 21.698 21.55 21.83173 21.402 21.025 21.688 21.546 21.83174 21.405 21.022 21.682 21.54 21.83475 21.381 21.002 21.69 21.539 21.83676 21.383 21.01 21.683 21.557 21.83277 21.384 21.02 21.68 21.57 21.82378 21.4 21.017 21.693 21.551 21.83679 21.388 21.022 21.701 21.571 21.83780 21.396 21.013 21.686 21.551 21.83581 21.398 21.025 21.679 21.553 21.83482 21.38 21.026 21.679 21.547 21.85183 21.396 21.013 21.695 21.55 21.85184 21.392 21.013 21.682 21.537 21.83485 21.392 21.006 21.698 21.553 21.83186 21.4 20.998 21.706 21.555 21.8387 21.394 21.005 21.703 21.571 21.82788 21.401 21.012 21.716 21.561 21.82789 21.388 20.992 21.694 21.574 21.83990 21.389 21.013 21.679 21.553 21.84591 21.367 21.013 21.699 21.544 21.83992 21.385 21.015 21.694 21.549 21.82493 21.387 21.008 21.68 21.545 21.83694 21.391 20.999 21.684 21.558 21.82195 21.394 20.995 21.684 21.539 21.83996 21.402 20.997 21.699 21.557 21.85497 21.387 20.982 21.677 21.551 21.82998 21.397 20.982 21.703 21.564 21.82699 21.391 20.983 21.681 21.571 21.824100 21.396 20.988 21.699 21.541 21.835101 21.381 20.992 21.687 21.551 21.82102 21.379 20.997 21.685 21.54 21.838103 21.379 21.013 21.673 21.547 21.834104 21.386 21.009 21.689 21.54 21.841105 21.388 20.999 21.697 21.546 21.821106 21.394 21.002 21.697 21.564 21.824107 21.384 20.992 21.69 21.564 21.83108 21.381 20.995 21.7 21.548 21.827109 21.368 20.983 21.7 21.545 21.817110 21.39 20.994 21.702 21.554 21.829111 21.388 21.015 21.706 21.555 21.82112 21.398 21.003 21.685 21.565 21.831113 21.408 21.019 21.691 21.546 21.831114 21.394 21.021 21.703 21.564 21.836115 21.404 21.012 21.704 21.562 21.837116 21.394 21.024 21.697 21.548 21.843117 21.374 21.017 21.686 21.544 21.845118 21.375 21.034 21.694 21.555 21.82119 21.374 21.033 21.693 21.551 21.83120 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.MIKEhttp://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.

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 59is 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.38Is that what your trying to do?MIKEhttp://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 60Then Column D from Row 1 thru Row 60, ETC, ETC.Or, do you want the Columns Averaged as a matrixSo 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?MIKEhttp://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.

Report •

#11
November 4, 2015 at 07:40:05
 DerbyDad03,I believe the OP did try to use the < PRE > tags in reply #4if you look at the last row of data you can see where he missedputting in the Left Arrow.There is still going to be a bit of confusion, because he starts hislist of measurements with line number Zero.So 60 seconds for his list is 0 thru 59.MIKEmessage 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 #4if you look at the last row of data you can see where he missedputting 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 hislist 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.

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? MIKEhttp://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 NumberColumn I is the Row End NumberWe 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 & 10and 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 N2and 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 ```MIKEhttp://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
 If you would rather do the Averaging on a second sheet, you can.Your sheets should be named Sheet1 & Sheet2Sheet1, 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 A1Put 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.MIKEhttp://www.skeptic.com/