+ Reply to Thread
Results 1 to 8 of 8

Automatically bin and manipulate a long list of numbers in excel?

  1. #1
    Registered User
    Join Date
    01-13-2018
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    4

    Automatically bin and manipulate a long list of numbers in excel?

    Apologies, I've searched a fair bit and got nowhere. I have a column of values from 0 upwards (e.g., 0, 0.1, 0.3, 0.456, 0.9, 1.34, 2.76,3, etc.) in excel and I want to search the column and automatically select and do operations on all the values from (for example) 0-0.5, then 0.5-1 etc. - essentially I'm binning the data, but I want to keep the individual values, and not just make a cumulative histogram.

    Additionally, this data is related to another set of values in the neighbouring column that I also want to manipulate (e.g. average) but using the selection/binning derived from the first column

    Right now I'm doing it manually but I've got loads of data and it's painful! Any help would be very much appreciated

    Below is an example of real data - I would like to bin the data by the distance column, but then average the corresponding values in the correl column

    Distance Correl
    0.107 0.231543191
    0.121 -0.113430804
    0.125 -0.225157221
    0.139 0.635249057
    0.297 0.187813622
    0.305 -0.039154062
    0.316 0.309709204
    0.324 0.053197458
    0.365 0.779907584
    0.431 0.578507777
    0.498 0.870453924
    0.567 0.569028982
    0.569 0.120788037
    0.591 -0.263311555
    0.592 0.176925089
    0.637 0.3832856
    0.647 0.071834483
    0.706 0.856682116
    0.712 0.646768393
    0.717 -0.506595899
    0.803 -0.228823135
    0.804 0.511026311
    0.909 0.104275562
    0.942 -0.576739195
    0.963 0.276765894
    1.062 0.269682337
    1.076 -0.460920592
    1.141 -0.794285815
    1.169 0.456195973
    1.176 -0.22648159
    1.204 0.767823774
    1.206 0.506337975
    1.248 -0.036141265
    1.276 -0.200561872
    1.435 0.212445922
    1.473 0.139369327
    1.506 -0.793431954
    1.509 -0.207908942
    1.572 -0.334646805
    1.581 -0.455062755
    1.613 0.118124798
    1.645 0.022347327
    1.654 -0.18488657
    1.691 -0.634942841
    1.846 0.305781134
    1.923 -0.233068097
    1.937 -0.760469503
    1.97 -0.405951943
    1.983 0.53117752
    2.079 -0.557570533
    2.108 -0.562887323
    2.116 0.430712971
    2.218 -0.108667593
    2.228 0.405687905
    2.238 -0.409477979
    2.239 -0.138086333
    2.258 -0.622501822
    2.307 0.62591918
    2.31 -0.282384088
    2.382 -0.446903361
    2.397 -0.557838331
    2.414 0.39462779
    2.417 -0.28957662
    2.432 0.05296109
    2.458 -0.188323824
    2.511 0.192054937
    2.535 0.454103375
    2.539 0.10958419
    2.544 0.552003623
    2.617 0.161215879
    2.7 -0.628111613
    2.708 -0.137851596
    2.726 0.652577067
    2.741 -0.591852139
    2.753 -0.717418662
    2.755 -0.437193755
    2.774 -0.746105319
    2.785 -0.158506219
    2.823 -0.065327884
    2.833 0.167969869
    2.865 0.650776099
    2.895 -0.343433684
    3.024 -0.053992702
    3.042 0.32993738
    3.052 -0.057049978
    3.08 0.284784516
    3.131 -0.133227298
    3.139 -0.746078299
    3.181 0.222684922
    3.2 0.864294214
    3.32 0.017813367
    3.421 -0.301927997
    3.432 0.849849325
    3.444 -0.185286859
    3.459 -0.341527052
    3.551 -0.016007498
    3.555 0.064670353
    3.68 0.050410638
    3.748 0.622336731
    3.786 -0.436609301
    3.893 -0.025019151
    3.92 -0.1227501
    3.929 0.619469346
    3.957 -0.430399821
    3.964 0.073662268
    4.045 0.288180971
    4.071 -0.409988186
    4.143 0.23203938
    4.198 -0.16424913
    4.226 -0.244002938
    4.228 -0.505008012
    4.262 -0.154164657
    4.268 0.467456711
    4.272 -0.300812803
    4.28 0.835725933
    4.387 0.296640573
    4.395 0.079720799
    4.476 -0.056803369
    4.496 0.415410868
    4.627 0.366984703
    4.635 0.613154387
    4.637 0.305826528
    4.696 -0.225994139
    4.697 0.310119021
    4.711 0.413862039
    4.724 -0.388860661
    4.793 -0.672312956
    4.816 0.203356194
    4.835 -0.306900526
    4.849 0.796330929
    4.927 0.033345638
    4.932 -0.081350764
    5.002 -0.277149917
    5.034 -0.752258287
    5.062 0.421856247
    5.123 -0.572102838
    5.133 0.351338201
    5.135 0.215865802
    5.14 -0.173041903
    5.196 0.361158504
    5.219 -0.095742061
    5.247 0.422775013
    5.29 -0.515457427
    5.344 -0.366564414
    5.358 -0.24879394
    5.367 0.095846543
    5.402 -0.393957383
    5.43 0.088752157
    5.438 -0.057388911
    5.439 -0.382072761
    5.441 0.111219577
    5.5 -0.345360823
    5.538 -0.267674995
    5.627 0.268629788
    5.639 -0.424303361
    5.702 -0.19968068
    5.735 0.551129338
    5.748 0.362258296
    5.788 -0.241980484
    5.841 -0.123681893
    5.865 -0.15306396
    5.913 -0.59096405
    6.004 -0.670616155
    6.185 -0.569891923
    6.196 0.234220006
    6.203 -0.575602784
    6.206 -0.569190207
    6.251 -0.241354828
    6.31 0.032031293
    6.317 0.189548277
    6.339 -0.336552254
    6.339 -0.46870209
    6.378 0.168568427
    6.388 -0.481981507
    6.501 -0.748951957
    6.503 -0.244979251
    6.508 0.369967092
    6.571 -0.38195858
    6.575 0.101147976
    6.615 0.086761763
    6.634 -0.168882365
    6.644 0.084456901
    6.682 -0.091871619
    6.694 0.342131321
    6.714 -0.562011046
    6.753 -0.214671172
    6.819 -0.035584872
    6.908 0.378217428
    6.939 0.569565149
    6.981 0.249521881
    7.006 0.635981247
    7.011 -0.579086741
    7.093 0.186065312
    7.095 -0.11845252
    7.113 0.473143499
    7.145 0.8095158
    7.252 0.313194134
    7.272 -0.178194671
    7.327 -0.074180004
    7.341 -0.800880197
    7.361 0.559250524
    7.393 -0.176212042
    7.411 -0.097435748
    7.437 0.519877071
    7.466 0.180349028
    7.548 0.086768103
    7.557 -0.235680119
    7.576 0.314911003
    7.651 -0.20936674
    7.658 -0.480855423
    7.677 0.089885397
    7.687 -0.152258236
    7.712 0.64979733
    7.758 0.286952104
    7.819 0.2388777
    7.823 0.454394156
    7.896 -0.10091786
    7.897 0.477241712
    7.92 0.186103348
    7.974 -0.290856583
    7.984 -0.219694104
    8.058 0.370149873
    8.143 0.482166155
    8.155 0.137844322
    8.185 -0.058609235
    8.188 0.477503575
    8.22 -0.810558274
    8.262 0.454336705
    8.327 -0.239766073
    8.49 -0.465652108
    8.567 0.389425976
    8.617 -0.220276671
    8.707 0.543497994
    8.742 -0.434768208
    8.828 0.361694413
    8.883 -0.0087694
    8.899 -0.724629122
    8.922 0.751446962
    8.959 0.327725038
    8.992 0.387526825
    9.047 -0.279725523
    9.211 0.279659249
    9.264 -0.484584587
    9.324 0.122659809
    9.331 -0.746199803
    9.334 0.078204643
    9.403 -0.696251304
    9.419 0.198121077
    9.42 0.872458286
    9.468 -0.017692833
    9.51 -0.153848345
    9.527 0.146621095
    9.545 -0.056815446
    9.559 0.505772433
    9.628 -0.150108223
    9.639 -0.568274493
    9.684 0.057877681
    9.766 0.658275065
    9.833 -0.035318162
    9.834 -0.321464182
    10.068 -0.423469371
    10.126 0.626670712
    10.135 -0.283542332
    10.137 -0.454008555
    10.174 0.139488408
    10.251 -0.170865335
    10.276 0.224195615
    10.413 0.152543315
    10.432 0.302349717
    10.465 -0.132191938
    10.5 -0.26058963
    10.537 -0.439322785
    10.572 0.172459865
    10.637 0.202494486
    10.646 0.438022655
    10.729 0.729519547
    10.797 0.696788464
    10.823 -0.011709512
    10.843 -0.387940525
    10.896 -0.045474399
    10.944 -0.020593648
    10.962 -0.057743273
    11.341 -0.273558515
    11.415 -0.830920062
    11.45 -0.501152417
    11.535 -0.312281677
    11.536 0.37739885
    11.54 0.318798167
    11.609 -0.417020605
    11.641 0.379919212
    11.661 -0.397253665
    11.706 -0.298641659
    11.722 0.39053013
    11.748 0.773671297
    11.755 0.17595165
    11.817 -0.475155591
    11.938 -0.559637603
    11.942 -0.278330441
    12.038 0.5080901
    12.045 0.214767352
    12.072 0.413716054
    12.127 -0.474024425
    12.252 0.631809004
    12.253 0.179029016
    12.291 0.311716984
    12.369 0.205969342
    12.383 -0.150744452
    12.392 0.763879438
    12.534 0.317360618
    12.607 -0.206247288
    12.685 0.711924761
    12.847 0.044067401
    12.879 -0.050256692
    12.885 -0.025756935
    12.954 0.302422177
    12.959 0.125254072
    13.004 0.12083152
    13.187 -0.20591674
    13.19 0.612723533
    13.254 0.566785964
    13.278 -0.046035649
    13.523 0.711237377
    13.552 -0.263375353
    13.596 0.265138753
    13.644 0.11360174
    13.688 0.526929851
    13.827 0.266287271
    13.847 -0.022186447
    13.954 0.389423533
    13.961 -0.235553325
    13.968 -0.706262995
    14.055 0.745391708
    14.075 0.309897126
    14.18 -0.38064226
    14.235 0.689440169
    14.266 -0.317820535
    14.352 0.230086916
    14.394 0.308140923
    14.477 -0.437704204
    14.53 0.35426975
    14.559 0.476855232
    14.65 -0.30387119
    14.666 0.656501531
    14.693 0.030985594
    14.764 -0.204895455
    14.772 -0.271978778
    14.8 -0.501965728
    14.835 -0.022935461
    14.903 0.237358476
    15.069 -0.570714205
    15.095 -0.487118935
    15.124 -0.132452186
    15.216 -0.304556803
    15.261 -0.11643017
    15.333 -0.387773616
    15.386 -0.093646915
    15.46 -0.590539988
    15.47 0.267761659
    15.472 -0.177497648
    15.581 -0.36076346
    15.712 0.281189945
    15.807 -0.584089486
    15.978 -0.095726126
    16.039 -0.224874924
    16.085 -0.114866593
    16.172 -0.684403258
    16.264 -0.800525368
    16.385 0.367641492
    16.888 -0.025941931
    16.976 -0.85267003
    17.107 -0.222236989
    17.147 -0.167124764
    17.161 -0.335051866
    17.185 -0.617759144
    17.232 0.016468826
    17.73 0.505563639
    17.824 0.06224494
    17.918 0.27245393
    18.039 0.118291344
    18.094 -0.525216317
    18.223 -0.464040923
    18.234 0.387653606
    18.323 -0.013438605
    18.355 -0.036980762
    18.62 -0.099563701
    18.63 0.213694231
    18.792 -0.11903634
    18.881 -0.075374285
    18.946 0.73771348
    19.002 0.669898561
    19.399 0.161720663
    19.529 -0.568392121
    19.593 -0.256950192
    19.696 -0.572039961
    19.94 0.22998271
    19.968 -0.694079694
    20.157 0.090160765
    20.265 -0.236934071
    20.278 -0.63284651
    20.462 0.395084347
    20.583 -0.115059872
    20.605 0.627531568
    20.869 -0.250437021
    20.96 0.304230332
    21.081 -0.218593069
    21.099 0.452382139
    21.174 0.401322489
    21.174 -0.440957627
    21.22 0.121019943
    21.375 -0.048404332
    21.666 0.389223646
    21.672 0.607778992
    21.787 -0.134478435
    21.811 0.563628699
    22.378 0.721651314
    22.451 0.721593361
    23.02 0.145689993
    23.357 -0.022197007
    23.478 -0.359016438
    24.069 -0.563237527
    24.419 -0.066911353
    24.54 0.108659909
    25.131 -0.285886621
    25.595 0.400883285
    25.716 -0.632932539
    25.892 -0.036476875
    26.013 -0.518186992
    26.307 0.87917994
    26.604 -0.036477855
    26.801 -0.076955073
    26.922 -0.324248427
    27.513 0.45605872
    28.647 -0.001909539
    28.768 0.420046754
    29.359 0.123853153

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Automatically bin and manipulate a long list of numbers in excel?

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    01-13-2018
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    4

    Re: Automatically bin and manipulate a long list of numbers in excel?

    Thanks John, apologies for not following protocol, thanks for the heads-up

    Attached is a file with the example data (before) and the data binned (after)
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Automatically bin and manipulate a long list of numbers in excel?

    In E2

    =AVERAGEIFS(A:A,$A:$A,">=" &$D2,$A:$A,"<=" & $D2+1)

    copy across to F and copy both cells down

    In D2

    =ROWS($1:1)-1
    Attached Files Attached Files
    Last edited by JohnTopley; 01-14-2018 at 12:59 PM.

  5. #5
    Registered User
    Join Date
    01-13-2018
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    4

    Re: Automatically bin and manipulate a long list of numbers in excel?

    Thanks John this is amazing! I’m slowly seeing how to use excel better.

    If you have time, I have a couple of more questions (+examples)

    Q1- is there a general way to do the above, but for any function? I can see how AVERAGEIFS is working, but if I want to (automatically) select the same group of cells and perform STDEV for example? (Before/After 1)

    Q2 – the formula you used to calculate the averages – is there a quick way to change the bin limits that makes sense in the formula?
    I’ve tried changing them to create bins of size five (see example) – but this doesn’t work
    (Before/After 2)

    Thanks again for any help, the first reply was super useful and is saving me a lot of time with my analysis
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Automatically bin and manipulate a long list of numbers in excel?

    Hello texinjapan and Welcome to Excel Forum.
    There isn't a standard deviation function equivalent to averageifs, however there is a way to get the results using the following array entered formula*:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    On the after 2 sheet the bin limits are changed to reflect the lower limit for each bin and 30 is added in row 8.
    The formulas for columns E and F are similar to the one that John supplied in post #4.
    The array entered formula for column G is the same as the one given above for standard deviation of a population with conditions.
    The formula for counting is: =COUNTIFS($A$2:$A$436,">="&$D2,$A$2:$A$436,"<"&$D3)
    *Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    01-13-2018
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    4

    Re: Automatically bin and manipulate a long list of numbers in excel?

    Hey JeteMc

    Apologies for late response, I have been busy working on other projects. Your comment and array was extremely useful! Thanks very much

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Automatically bin and manipulate a long list of numbers in excel?

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools drop down on the ribbon above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 02-27-2014, 07:04 PM
  2. [SOLVED] (How to) Transpose one long list of numbers in CSV file to a number of rows
    By sfmatt13 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 10-30-2012, 07:35 PM
  3. [SOLVED] Need Help w/ Excel Macro To Manipulate Mailing List... Most Of The Way There, I Think....
    By SeanEboy in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 05-18-2012, 01:11 PM
  4. Find numbers in a long list that add up to a specified total
    By AMFISH in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-06-2012, 01:02 AM
  5. Replies: 2
    Last Post: 07-09-2010, 01:55 PM
  6. Replies: 6
    Last Post: 01-13-2010, 10:26 AM
  7. Highlight 5 largest numbers in a long list
    By Manosh in forum Excel General
    Replies: 1
    Last Post: 04-15-2006, 10:15 AM
  8. Replies: 0
    Last Post: 02-15-2006, 02:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1