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
Bookmarks