Spreadsheet: 753997d1635874781-single-formula-to-average-numbers-based-on-percentile-quintile-single-formula-to-average-numbers-based-on-percentile-quintile
Sheet: Data

 |  | Securities | percentiles |  | range | average | averageif / [NAME] | check |  | consolidated formula
 |  | -3.54222129887686 |  |  | 0 - 20%
 |  | -0.842215534368246 |  |  | 20% - 40% |  |  |  |  | =average(if(percentile.inc(C4:C1000,0.2),
 |  | -0.721676374190503 |  |  | 40% - 60%
 |  | -0.57844921507897 |  |  | 60% - 80%
 |  | -0.445008892255954 |  |  | 80% - 100%
 |  | -0.183172985779378
 |  | -0.150906383082052
 |  | -0.138544586502261 |  |  |  |  |  |  | percentiles | =Data!$D$4:INDEX(Data!$D:$D,MATCH(25^25,Data!$D:$D))
 |  | -0.0890361253718201 |  |  |  |  |  |  | Securities | =Data!$C$4:INDEX(Data!$C:$C,MATCH(25^25,Data!$C:$C))
 |  | -0.0663638735046366
 |  | -0.0620400413491303
 |  | -0.046816148681919
 |  | -0.0352248667123236
 |  | -0.0348932062137753
 |  | -0.0116771147735146
 |  | -0.0109795173266913
 |  | 0.00367001683303805
 |  | 0.0208433714452568
 |  | 0.0332755482714923
 |  | 0.0350790698678944
 |  | 0.036550687732228
 |  | 0.041085432058866
 |  | 0.058773368495088
 |  | 0.0620786746625449
 |  | 0.0673993268409452
 |  | 0.0977740345925388
 |  | 0.100147578090075
 |  | 0.10114188095253
 |  | 0.105575423736065
 |  | 0.108107501190485
 |  | 0.116467738484717
 |  | 0.124748688165816
 |  | 0.132087756961543
 |  | 0.136746772082069
 |  | 0.146515266160209
 |  | 0.147070880010403
 |  | 0.15082735554272
 |  | 0.153332766669816
 |  | 0.165041114834268
 |  | 0.167429551251185
 |  | 0.169736187248622
 |  | 0.173453258084968
 |  | 0.176337399737521
 |  | 0.181346411399398
 |  | 0.181385366706745
 |  | 0.185213322750618
 |  | 0.192389329806043
 |  | 0.204882898581621
 |  | 0.205267282964296
 |  | 0.208283219319905
 |  | 0.215283032179594
 |  | 0.221797362567396
 |  | 0.222702963929425
 |  | 0.234349210405318
 |  | 0.23447615499356
 |  | 0.245356786731994
 |  | 0.253233823421225
 |  | 0.254167847900047
 |  | 0.259100237922317
 |  | 0.259873648080948
 |  | 0.277131029274394
 |  | 0.278015451451917
 |  | 0.278143074015689
 |  | 0.280443662019258
 |  | 0.28611288333623
 |  | 0.28814443695808
 |  | 0.291804955257984
 |  | 0.29916064921131
 |  | 0.299449029619055
 |  | 0.301548068030465
 |  | 0.307390192946666
 |  | 0.307450145241595
 |  | 0.308918929518643
 |  | 0.31220047585656
 |  | 0.321322773701588
 |  | 0.33063876931146
 |  | 0.335522240254968
 |  | 0.339686039776752
 |  | 0.340490353235769
 |  | 0.341736475314776
 |  | 0.342700237592042
 |  | 0.345027829501913
 |  | 0.346429058385446
 |  | 0.347386123979079
 |  | 0.347789728125676
 |  | 0.348622335975077
 |  | 0.353452077752045
 |  | 0.363195249743359
 |  | 0.364472565827029
 |  | 0.366634194802653
 |  | 0.369792578067174
 |  | 0.370233127585152
 |  | 0.372698434342799
 |  | 0.378670859393914
 |  | 0.382890983434626
 |  | 0.386791328294141
 |  | 0.388826906954176
 |  | 0.391004440935235
 |  | 0.392027703816713
 |  | 0.39388887906542
 |  | 0.39605735977801
 |  | 0.400374770948002
 |  | 0.40234926459053
 |  | 0.409578392744934
 |  | 0.416429855969603
 |  | 0.417699121386043
 |  | 0.419949134865936
 |  | 0.42266532945597
 |  | 0.423700027067442
 |  | 0.424362635911874
 |  | 0.426221022446403
 |  | 0.428870532234472
 |  | 0.429513955577241
 |  | 0.430408067512933
 |  | 0.431397999246581
 |  | 0.432738267654599
 |  | 0.433402889253779
 |  | 0.437575041223281
 |  | 0.438124711867687
 |  | 0.438462681413921
 |  | 0.444631018080688
 |  | 0.446120854267676
 |  | 0.447636797089265
 |  | 0.449675039388319
 |  | 0.449937326709007
 |  | 0.453503319915656
 |  | 0.457777941167728
 |  | 0.467216310595621
 |  | 0.46909447940253
 |  | 0.472598521856815
 |  | 0.474357073006379
 |  | 0.477666654938129
 |  | 0.479660108829835
 |  | 0.483783822157819
 |  | 0.48423567575432
 |  | 0.484319644980209
 |  | 0.485427601239853
 |  | 0.490595146253623
 |  | 0.497683226204524
 |  | 0.497752486054158
 |  | 0.498010378748377
 |  | 0.503032933679303
 |  | 0.507103843912378
 |  | 0.507258589883952
 |  | 0.509357075913933
 |  | 0.51087455923178
 |  | 0.513704320130515
 |  | 0.517551866941997
 |  | 0.518550563383541
 |  | 0.519296764994386
 |  | 0.52079825302417
 |  | 0.522451416164572
 |  | 0.525862389803459
 |  | 0.526354577069964
 |  | 0.526823057845839
 |  | 0.527069105132031
 |  | 0.530735964606086
 |  | 0.530998923660435
 |  | 0.535605351974649
 |  | 0.542742397092824
 |  | 0.543028696439131
 |  | 0.547165461574376
 |  | 0.547303894839016
 |  | 0.54952862724918
 |  | 0.551007787346509
 |  | 0.555928328111068
 |  | 0.560604812399392
 |  | 0.561252822675764
 |  | 0.567245041748097
 |  | 0.572513446554559
 |  | 0.57506335469371
 |  | 0.575171394747748
 |  | 0.580131768891617
 |  | 0.583243951768324
 |  | 0.587896834474112
 |  | 0.588719756878491
 |  | 0.593797691751569
 |  | 0.595850946154519
 |  | 0.597753046197654
 |  | 0.597797272938293
 |  | 0.598301566358344
 |  | 0.602328031664621
 |  | 0.614034273003149
 |  | 0.620051155880647
 |  | 0.621451077940624
 |  | 0.621529173944273
 |  | 0.621613177079028
 |  | 0.6330247490436
 |  | 0.633205747249246
 |  | 0.63441170385722
 |  | 0.635499325968159
 |  | 0.637406280365639
 |  | 0.638867931545264
 |  | 0.63982491915912
 |  | 0.640431482168475
 |  | 0.641706130588757
 |  | 0.643933800068181
 |  | 0.649611414638746
 |  | 0.653849671231232
 |  | 0.655002113549642
 |  | 0.65813137767547
 |  | 0.658780420552879
 |  | 0.662724652980212
 |  | 0.668490463689669
 |  | 0.669123863546469
 |  | 0.671163506969653
 |  | 0.674338555351003
 |  | 0.676751387813226
 |  | 0.680330962758007
 |  | 0.68795570421423
 |  | 0.689802118640628
 |  | 0.693910977852357
 |  | 0.695006528079181
 |  | 0.696414305922706
 |  | 0.698130450119221
 |  | 0.698720224232388
 |  | 0.699420107668748
 |  | 0.699676901138532
 |  | 0.705698688015612
 |  | 0.707916269981113
 |  | 0.710480091162077
 |  | 0.711566551467485
 |  | 0.714115656641477
 |  | 0.721164765055883
 |  | 0.726977229096907
 |  | 0.728463105762673
 |  | 0.728524791136876
 |  | 0.729162639797057
 |  | 0.729964121503481
 |  | 0.73150053286025
 |  | 0.733890423612022
 |  | 0.734529370889139
 |  | 0.739751866061149
 |  | 0.740783532498664
 |  | 0.746073637236489
 |  | 0.74834515933129
 |  | 0.749687173518655
 |  | 0.74988789478365
 |  | 0.750988970089972
 |  | 0.755525002211466
 |  | 0.760867823517183
 |  | 0.763889768617553
 |  | 0.766486792822227
 |  | 0.766559350225134
 |  | 0.768397791893011
 |  | 0.770331539825103
 |  | 0.777742720405928
 |  | 0.782760087612187
 |  | 0.783018506930062
 |  | 0.783021895424129
 |  | 0.783317108553812
 |  | 0.786011905283879
 |  | 0.788759527691241
 |  | 0.789047086949445
 |  | 0.789819541770417
 |  | 0.789829678071879
 |  | 0.790010983455858
 |  | 0.792439619965247
 |  | 0.793080320983261
 |  | 0.795562445034763
 |  | 0.798383868533931
 |  | 0.798914990066533
 |  | 0.799824447547266
 |  | 0.800232344534073
 |  | 0.802133539044984
 |  | 0.807909449201214
 |  | 0.816367670617655
 |  | 0.817517117282642
 |  | 0.818705656423883
 |  | 0.819630497518996
 |  | 0.820889696023382
 |  | 0.821307877140522
 |  | 0.821549180592747
 |  | 0.821718844986399
 |  | 0.823451156264079
 |  | 0.823610121963295
 |  | 0.823692832755041
 |  | 0.826266865038447
 |  | 0.828195352654937
 |  | 0.831464513706166
 |  | 0.831718183791274
 |  | 0.833823150398019
 |  | 0.835663081217648
 |  | 0.837798983654882
 |  | 0.841345633681285
 |  | 0.842457618398492
 |  | 0.849301917666656
 |  | 0.849974251697423
 |  | 0.851884795196997
 |  | 0.8524175643614
 |  | 0.856752234532631
 |  | 0.861318641265503
 |  | 0.862460153030235
 |  | 0.862685381201256
 |  | 0.863188170762862
 |  | 0.864379780475281
 |  | 0.86473519260638
 |  | 0.87108164051442
 |  | 0.87161243350393
 |  | 0.876800646712194
 |  | 0.87805987152385
 |  | 0.882359606316126
 |  | 0.883222332788315
 |  | 0.887862573440823
 |  | 0.888507442691046
 |  | 0.888585203810797
 |  | 0.89010832593885
 |  | 0.890623645302572
 |  | 0.894261962643931
 |  | 0.895341160838132
 |  | 0.895845938404754
 |  | 0.896107169805173
 |  | 0.896731348706584
 |  | 0.896971733934868
 |  | 0.903957271946583
 |  | 0.904103524123334
 |  | 0.904150476515296
 |  | 0.907097999696181
 |  | 0.908797783867768
 |  | 0.910359741558979
 |  | 0.911248668401831
 |  | 0.917667001426442
 |  | 0.918161882638568
 |  | 0.923100967220418
 |  | 0.923109212071199
 |  | 0.926491730434069
 |  | 0.928336119890881
 |  | 0.930182434489269
 |  | 0.935295270242178
 |  | 0.938360725123606
 |  | 0.939658332319403
 |  | 0.940975360424068
 |  | 0.943570261198272
 |  | 0.945530100244025
 |  | 0.948505501404767
 |  | 0.948895996245299
 |  | 0.94977148665155
 |  | 0.951148937079531
 |  | 0.952479655035776
 |  | 0.953445653048579
 |  | 0.954412701727009
 |  | 0.954520061622572
 |  | 0.955056424620412
 |  | 0.957491570544054
 |  | 0.959027676977059
 |  | 0.959393797639014
 |  | 0.962127293059068
 |  | 0.965178991871393
 |  | 0.969049604677545
 |  | 0.970589143511367
 |  | 0.970707863687492
 |  | 0.971404152054737
 |  | 0.977884818685802
 |  | 0.978293926237481
 |  | 0.980739640708822
 |  | 0.981405551867464
 |  | 0.984388281795356
 |  | 0.984575293788641
 |  | 0.984811612800585
 |  | 0.986880591404268
 |  | 0.987032684335299
 |  | 0.993577827799168
 |  | 0.99615989720966
 |  | 0.999724641394474
 |  | 1.00209384482349
 |  | 1.00513667687935
 |  | 1.00844387351269
 |  | 1.01687635863555
 |  | 1.0183748738249
 |  | 1.02031310108038
 |  | 1.02513577045728
 |  | 1.02514709885543
 |  | 1.03003463632216
 |  | 1.03347963847725
 |  | 1.03695112756373
 |  | 1.03766993082523
 |  | 1.04073094048985
 |  | 1.04184412328309
 |  | 1.04620329476877
 |  | 1.04865368948071
 |  | 1.04879165074014
 |  | 1.05285951826647
 |  | 1.05416603064679
 |  | 1.05565476741738
 |  | 1.05573256628873
 |  | 1.05738062738935
 |  | 1.06016867771579
 |  | 1.06345519065678
 |  | 1.06726939610114
 |  | 1.07547051026021
 |  | 1.0759497177444
 |  | 1.07786446398056
 |  | 1.08006047970252
 |  | 1.08238311264203
 |  | 1.08325507534609
 |  | 1.08671143727448
 |  | 1.08781762296576
 |  | 1.08786367408814
 |  | 1.08929831301091
 |  | 1.09453523719539
 |  | 1.09695886708701
 |  | 1.10108468350589
 |  | 1.10122683370141
 |  | 1.10185806473263
 |  | 1.1019065437786
 |  | 1.10695169364792
 |  | 1.10778504596967
 |  | 1.11038389208241
 |  | 1.11067501947322
 |  | 1.11149867707661
 |  | 1.11822633264956
 |  | 1.12000696158186
 |  | 1.12199469493817
 |  | 1.12578880312076
 |  | 1.12927810751953
 |  | 1.13057410454096
 |  | 1.13239003080984
 |  | 1.13380179562336
 |  | 1.13441007233326
 |  | 1.14000684934888
 |  | 1.14188999857188
 |  | 1.14314038890958
 |  | 1.14496628020996
 |  | 1.14812281576079
 |  | 1.15017563840777
 |  | 1.15282580246978
 |  | 1.15315877464689
 |  | 1.15398747697507
 |  | 1.15552046100825
 |  | 1.15618673370818
 |  | 1.15845732576758
 |  | 1.15918078711439
 |  | 1.16562957811055
 |  | 1.16654418433226
 |  | 1.16831254317538
 |  | 1.16909805643338
 |  | 1.17088625505657
 |  | 1.17142235633649
 |  | 1.17318204632469
 |  | 1.17944460124995
 |  | 1.18303119492104
 |  | 1.18305718897381
 |  | 1.18472590613257
 |  | 1.18527357640352
 |  | 1.18818495455331
 |  | 1.19150906370448
 |  | 1.20136349233475
 |  | 1.20174710282627
 |  | 1.20277825313806
 |  | 1.2045704885187
 |  | 1.20513011280645
 |  | 1.20525122369257
 |  | 1.20567398068226
 |  | 1.20606530245917
 |  | 1.20856220030657
 |  | 1.22211147047828
 |  | 1.22878643129641
 |  | 1.23196800728448
 |  | 1.23239515377894
 |  | 1.23255618160901
 |  | 1.23447426872048
 |  | 1.23490851909906
 |  | 1.23555431602175
 |  | 1.2520065357572
 |  | 1.25256690479632
 |  | 1.25676039139421
 |  | 1.25868022638329
 |  | 1.2588574944949
 |  | 1.26431509364704
 |  | 1.26440682096101
 |  | 1.27348338629818
 |  | 1.27583409586398
 |  | 1.27781510628041
 |  | 1.27795385452477
 |  | 1.28122877451232
 |  | 1.28263866311483
 |  | 1.2837111696043
 |  | 1.28393606706622
 |  | 1.28631350900202
 |  | 1.288392711005
 |  | 1.2914034231401
 |  | 1.29770381877492
 |  | 1.30583592221167
 |  | 1.30857185562745
 |  | 1.30894582667486
 |  | 1.31241871837976
 |  | 1.31248876071932
 |  | 1.31351368358106
 |  | 1.31478827610055
 |  | 1.31568700323525
 |  | 1.31905700573937
 |  | 1.32085151333279
 |  | 1.32296362284913
 |  | 1.32324838459362
 |  | 1.32423591914218
 |  | 1.32691403877703
 |  | 1.33255170179712
 |  | 1.33381936446279
 |  | 1.3341386665106
 |  | 1.33648677047878
 |  | 1.33699075256592
 |  | 1.33951409249441
 |  | 1.33961137560417
 |  | 1.34112609578096
 |  | 1.34754182642492
 |  | 1.35235623458146
 |  | 1.35570405928444
 |  | 1.35887366155639
 |  | 1.36299303288339
 |  | 1.36325497377081
 |  | 1.36625408461734
 |  | 1.36670690742276
 |  | 1.36766067859829
 |  | 1.36954397120247
 |  | 1.37124199242598
 |  | 1.37271118130007
 |  | 1.37623713312356
 |  | 1.37987404874531
 |  | 1.38996383658524
 |  | 1.39072181102261
 |  | 1.39597715516747
 |  | 1.39605590904775
 |  | 1.39703388334643
 |  | 1.4048670062974
 |  | 1.40499506152861
 |  | 1.42014730660961
 |  | 1.42051058161763
 |  | 1.42173426841784
 |  | 1.42390329949796
 |  | 1.42409395694682
 |  | 1.4247445824142
 |  | 1.42511746513742
 |  | 1.42901781780988
 |  | 1.43019652931141
 |  | 1.43615782921842
 |  | 1.43646254395763
 |  | 1.44987055219665
 |  | 1.45138484879107
 |  | 1.45730508631718
 |  | 1.45962580464946
 |  | 1.46434314842798
 |  | 1.46439818625019
 |  | 1.48212629812529
 |  | 1.48312268075111
 |  | 1.48647919486407
 |  | 1.48703237130867
 |  | 1.487683597427
 |  | 1.49167617871182
 |  | 1.49241042493135
 |  | 1.4934947837583
 |  | 1.49827517963391
 |  | 1.51145053364665
 |  | 1.52049484076064
 |  | 1.52521895463986
 |  | 1.52931397720363
 |  | 1.53404212938698
 |  | 1.54538158383295
 |  | 1.54544492584935
 |  | 1.54545132818667
 |  | 1.54678521114477
 |  | 1.54750134085754
 |  | 1.54788954756888
 |  | 1.54892322112353
 |  | 1.55235868328581
 |  | 1.55327018969932
 |  | 1.55897592993102
 |  | 1.56742411776725
 |  | 1.56776915814259
 |  | 1.57383145321424
 |  | 1.57428166906714
 |  | 1.57532567244823
 |  | 1.58733272562401
 |  | 1.58883540005691
 |  | 1.59172117876351
 |  | 1.60264702235082
 |  | 1.60389831242406
 |  | 1.60579345036275
 |  | 1.60581770240091
 |  | 1.61338592883091
 |  | 1.61653888725392
 |  | 1.61982900362078
 |  | 1.62381729702282
 |  | 1.63116098901889
 |  | 1.63367095927129
 |  | 1.6358950807078
 |  | 1.63852563324662
 |  | 1.63936951162338
 |  | 1.63955838079034
 |  | 1.64130461045198
 |  | 1.64226375308952
 |  | 1.64759253359447
 |  | 1.64993295307969
 |  | 1.65109012424811
 |  | 1.65477963390391
 |  | 1.65884301133181
 |  | 1.66768968817874
 |  | 1.67935722986054
 |  | 1.68871942093739
 |  | 1.69530631377163
 |  | 1.7034671793154
 |  | 1.70529809119359
 |  | 1.70789907649446
 |  | 1.71203936095968
 |  | 1.71399024358087
 |  | 1.71802712281715
 |  | 1.72065175128083
 |  | 1.7263191868555
 |  | 1.72668020836175
 |  | 1.72769925905265
 |  | 1.73033318389088
 |  | 1.73225258965814
 |  | 1.7324849784765
 |  | 1.7325864429772
 |  | 1.73446950682962
 |  | 1.75270954510295
 |  | 1.75770819707844
 |  | 1.7605146811101
 |  | 1.76315932891214
 |  | 1.76497465455923
 |  | 1.76746982039032
 |  | 1.76775065740686
 |  | 1.77338847628545
 |  | 1.77671536348568
 |  | 1.7782354263977
 |  | 1.77889791218936
 |  | 1.78590857694431
 |  | 1.78635800688347
 |  | 1.7906995773102
 |  | 1.79135303713235
 |  | 1.80727989488922
 |  | 1.81381669286424
 |  | 1.81489105884099
 |  | 1.81521602068075
 |  | 1.8168830080484
 |  | 1.81824978988026
 |  | 1.81869512035187
 |  | 1.82180177076021
 |  | 1.82813798389895
 |  | 1.82936849594871
 |  | 1.83239352650186
 |  | 1.83306650356356
 |  | 1.84724264374281
 |  | 1.84729648832223
 |  | 1.84983003423207
 |  | 1.85132596539209
 |  | 1.8637957219226
 |  | 1.8644124625271
 |  | 1.86443562111494
 |  | 1.87327820690685
 |  | 1.87601530731279
 |  | 1.88723037218338
 |  | 1.89137897081082
 |  | 1.89349379349564
 |  | 1.91205304374329
 |  | 1.91556718694461
 |  | 1.91609955148795
 |  | 1.92137250675113
 |  | 1.93000399350302
 |  | 1.93428413126712
 |  | 1.93433131962226
 |  | 1.94015234801518
 |  | 1.94432125032088
 |  | 1.94477394184704
 |  | 1.94598977014076
 |  | 1.97251049059126
 |  | 1.98337649555135
 |  | 1.9905923834123
 |  | 2.00365443987803
 |  | 2.01051618744797
 |  | 2.01287868652298
 |  | 2.04210007454758
 |  | 2.04335379458856
 |  | 2.0462090533412
 |  | 2.06215637129211
 |  | 2.06563377090833
 |  | 2.06969743718131
 |  | 2.07805884802354
 |  | 2.09196698624127
 |  | 2.09517033670576
 |  | 2.10023173879157
 |  | 2.11055240229809
 |  | 2.1239956445353
 |  | 2.14937277781716
 |  | 2.15796991110486
 |  | 2.28014726642089
 |  | 2.31022129688748
 |  | 2.31399238184242
 |  | 2.35193404690179
 |  | 2.36371503590737
 |  | 2.36822217943358
 |  | 2.3827567951461
 |  | 2.38482140105599
 |  | 2.41872874020799
 |  | 2.42095569938538
 |  | 2.54995737427118
 |  | 2.56556435338771
 |  | 2.61453005043725
 |  | 2.61550263547643
 |  | 2.67213140341848
 |  | 2.85236179464516
 |  | 3.04469793595112