Excel - calculate quartile and median to generate box plot given a list of values and counts












0














I have a rather simple task to achieve but I am not sure how to do that in excel.



I have a list of values and number of counts of each value (my variable is discrete). I am trying to calculate 1st and 3rd quartiles of the variable given the counts I have. Overall dataset will be too large to fit into excel in raw format so I am using matrix of value counts.



Example:



Value | Count
1 | 100000
2 | 300000
3 | 350000
4 | 100000
5 | 50000
6 | 1000
7 | 10


I need to generate 1st, 3rd quartile and median out of this dataset, but as far as I see excel's quartile accepts only raw values, not values and their counts.










share|improve this question



























    0














    I have a rather simple task to achieve but I am not sure how to do that in excel.



    I have a list of values and number of counts of each value (my variable is discrete). I am trying to calculate 1st and 3rd quartiles of the variable given the counts I have. Overall dataset will be too large to fit into excel in raw format so I am using matrix of value counts.



    Example:



    Value | Count
    1 | 100000
    2 | 300000
    3 | 350000
    4 | 100000
    5 | 50000
    6 | 1000
    7 | 10


    I need to generate 1st, 3rd quartile and median out of this dataset, but as far as I see excel's quartile accepts only raw values, not values and their counts.










    share|improve this question

























      0












      0








      0







      I have a rather simple task to achieve but I am not sure how to do that in excel.



      I have a list of values and number of counts of each value (my variable is discrete). I am trying to calculate 1st and 3rd quartiles of the variable given the counts I have. Overall dataset will be too large to fit into excel in raw format so I am using matrix of value counts.



      Example:



      Value | Count
      1 | 100000
      2 | 300000
      3 | 350000
      4 | 100000
      5 | 50000
      6 | 1000
      7 | 10


      I need to generate 1st, 3rd quartile and median out of this dataset, but as far as I see excel's quartile accepts only raw values, not values and their counts.










      share|improve this question













      I have a rather simple task to achieve but I am not sure how to do that in excel.



      I have a list of values and number of counts of each value (my variable is discrete). I am trying to calculate 1st and 3rd quartiles of the variable given the counts I have. Overall dataset will be too large to fit into excel in raw format so I am using matrix of value counts.



      Example:



      Value | Count
      1 | 100000
      2 | 300000
      3 | 350000
      4 | 100000
      5 | 50000
      6 | 1000
      7 | 10


      I need to generate 1st, 3rd quartile and median out of this dataset, but as far as I see excel's quartile accepts only raw values, not values and their counts.







      excel quartile






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Nov 12 '18 at 4:45









      Maksim Khaitovich

      2,61621947




      2,61621947
























          2 Answers
          2






          active

          oldest

          votes


















          2














          Assuming the table as you give it is in A1:B8 (with headers in row 1), for the median you can use the following array formula**:



          =SUM(LOOKUP(INT(SUM(B2:B8)/2+{0.5,1}),MMULT(N(ROW(B2:B8)>=TRANSPOSE(ROW(B2:B8))),N(+B1:B7))+1,A2:A8))/2



          Note the one offset range (B1:B7) here, where B1 is assumed, as mentioned, to contain a (text) header.



          Edit: Re quartiles, that would depend on whether you are wishing to calculate these as per the Excel QUARTILE function or as per their 'standard' definition, i.e. as medians of the upper/lower half of the data (see here).



          Regards



          **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).






          share|improve this answer































            1














            I dont think this is possible only using formulas. You can easily create formulas to have the separate lists to avoid problem of having more total lines than excel rows, but I dont know about any way to append these lists into one big list inside functions. MAybe someone else knows a way, but in the meantime there is another way in Excel:



            What you can use is power query and power pivot. You start with the table that you have and add it as to Queries (Data - "Get & Transform Data" section - "From Table/Range". I have Excel 2016 so if you have a different version the path is most probably different or in older versions these tools are not available)



            Now edit the query and add another column using List.Repeat to create your full dataset, expand the values and load to data model (when you are in Query Editor go "Home" - "Close & Load", click the small arrow down and select . "Close & Load To..." and select "Only Create Connection" and check "Add this data to the Data Model")



            Inside the "Power Pivot" tab you can add a new Measure to calculate the quartile for this table. I used



            quartile:=PERCENTILE.EXC(AllData[AllValues];0,25)


            as I dont think there is really special quartile function.






            share|improve this answer























              Your Answer






              StackExchange.ifUsing("editor", function () {
              StackExchange.using("externalEditor", function () {
              StackExchange.using("snippets", function () {
              StackExchange.snippets.init();
              });
              });
              }, "code-snippets");

              StackExchange.ready(function() {
              var channelOptions = {
              tags: "".split(" "),
              id: "1"
              };
              initTagRenderer("".split(" "), "".split(" "), channelOptions);

              StackExchange.using("externalEditor", function() {
              // Have to fire editor after snippets, if snippets enabled
              if (StackExchange.settings.snippets.snippetsEnabled) {
              StackExchange.using("snippets", function() {
              createEditor();
              });
              }
              else {
              createEditor();
              }
              });

              function createEditor() {
              StackExchange.prepareEditor({
              heartbeatType: 'answer',
              autoActivateHeartbeat: false,
              convertImagesToLinks: true,
              noModals: true,
              showLowRepImageUploadWarning: true,
              reputationToPostImages: 10,
              bindNavPrevention: true,
              postfix: "",
              imageUploader: {
              brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
              contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
              allowUrls: true
              },
              onDemand: true,
              discardSelector: ".discard-answer"
              ,immediatelyShowMarkdownHelp:true
              });


              }
              });














              draft saved

              draft discarded


















              StackExchange.ready(
              function () {
              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53256089%2fexcel-calculate-quartile-and-median-to-generate-box-plot-given-a-list-of-value%23new-answer', 'question_page');
              }
              );

              Post as a guest















              Required, but never shown

























              2 Answers
              2






              active

              oldest

              votes








              2 Answers
              2






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes









              2














              Assuming the table as you give it is in A1:B8 (with headers in row 1), for the median you can use the following array formula**:



              =SUM(LOOKUP(INT(SUM(B2:B8)/2+{0.5,1}),MMULT(N(ROW(B2:B8)>=TRANSPOSE(ROW(B2:B8))),N(+B1:B7))+1,A2:A8))/2



              Note the one offset range (B1:B7) here, where B1 is assumed, as mentioned, to contain a (text) header.



              Edit: Re quartiles, that would depend on whether you are wishing to calculate these as per the Excel QUARTILE function or as per their 'standard' definition, i.e. as medians of the upper/lower half of the data (see here).



              Regards



              **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).






              share|improve this answer




























                2














                Assuming the table as you give it is in A1:B8 (with headers in row 1), for the median you can use the following array formula**:



                =SUM(LOOKUP(INT(SUM(B2:B8)/2+{0.5,1}),MMULT(N(ROW(B2:B8)>=TRANSPOSE(ROW(B2:B8))),N(+B1:B7))+1,A2:A8))/2



                Note the one offset range (B1:B7) here, where B1 is assumed, as mentioned, to contain a (text) header.



                Edit: Re quartiles, that would depend on whether you are wishing to calculate these as per the Excel QUARTILE function or as per their 'standard' definition, i.e. as medians of the upper/lower half of the data (see here).



                Regards



                **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).






                share|improve this answer


























                  2












                  2








                  2






                  Assuming the table as you give it is in A1:B8 (with headers in row 1), for the median you can use the following array formula**:



                  =SUM(LOOKUP(INT(SUM(B2:B8)/2+{0.5,1}),MMULT(N(ROW(B2:B8)>=TRANSPOSE(ROW(B2:B8))),N(+B1:B7))+1,A2:A8))/2



                  Note the one offset range (B1:B7) here, where B1 is assumed, as mentioned, to contain a (text) header.



                  Edit: Re quartiles, that would depend on whether you are wishing to calculate these as per the Excel QUARTILE function or as per their 'standard' definition, i.e. as medians of the upper/lower half of the data (see here).



                  Regards



                  **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).






                  share|improve this answer














                  Assuming the table as you give it is in A1:B8 (with headers in row 1), for the median you can use the following array formula**:



                  =SUM(LOOKUP(INT(SUM(B2:B8)/2+{0.5,1}),MMULT(N(ROW(B2:B8)>=TRANSPOSE(ROW(B2:B8))),N(+B1:B7))+1,A2:A8))/2



                  Note the one offset range (B1:B7) here, where B1 is assumed, as mentioned, to contain a (text) header.



                  Edit: Re quartiles, that would depend on whether you are wishing to calculate these as per the Excel QUARTILE function or as per their 'standard' definition, i.e. as medians of the upper/lower half of the data (see here).



                  Regards



                  **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Nov 12 '18 at 9:28

























                  answered Nov 12 '18 at 8:46









                  XOR LX

                  7,1421913




                  7,1421913

























                      1














                      I dont think this is possible only using formulas. You can easily create formulas to have the separate lists to avoid problem of having more total lines than excel rows, but I dont know about any way to append these lists into one big list inside functions. MAybe someone else knows a way, but in the meantime there is another way in Excel:



                      What you can use is power query and power pivot. You start with the table that you have and add it as to Queries (Data - "Get & Transform Data" section - "From Table/Range". I have Excel 2016 so if you have a different version the path is most probably different or in older versions these tools are not available)



                      Now edit the query and add another column using List.Repeat to create your full dataset, expand the values and load to data model (when you are in Query Editor go "Home" - "Close & Load", click the small arrow down and select . "Close & Load To..." and select "Only Create Connection" and check "Add this data to the Data Model")



                      Inside the "Power Pivot" tab you can add a new Measure to calculate the quartile for this table. I used



                      quartile:=PERCENTILE.EXC(AllData[AllValues];0,25)


                      as I dont think there is really special quartile function.






                      share|improve this answer




























                        1














                        I dont think this is possible only using formulas. You can easily create formulas to have the separate lists to avoid problem of having more total lines than excel rows, but I dont know about any way to append these lists into one big list inside functions. MAybe someone else knows a way, but in the meantime there is another way in Excel:



                        What you can use is power query and power pivot. You start with the table that you have and add it as to Queries (Data - "Get & Transform Data" section - "From Table/Range". I have Excel 2016 so if you have a different version the path is most probably different or in older versions these tools are not available)



                        Now edit the query and add another column using List.Repeat to create your full dataset, expand the values and load to data model (when you are in Query Editor go "Home" - "Close & Load", click the small arrow down and select . "Close & Load To..." and select "Only Create Connection" and check "Add this data to the Data Model")



                        Inside the "Power Pivot" tab you can add a new Measure to calculate the quartile for this table. I used



                        quartile:=PERCENTILE.EXC(AllData[AllValues];0,25)


                        as I dont think there is really special quartile function.






                        share|improve this answer


























                          1












                          1








                          1






                          I dont think this is possible only using formulas. You can easily create formulas to have the separate lists to avoid problem of having more total lines than excel rows, but I dont know about any way to append these lists into one big list inside functions. MAybe someone else knows a way, but in the meantime there is another way in Excel:



                          What you can use is power query and power pivot. You start with the table that you have and add it as to Queries (Data - "Get & Transform Data" section - "From Table/Range". I have Excel 2016 so if you have a different version the path is most probably different or in older versions these tools are not available)



                          Now edit the query and add another column using List.Repeat to create your full dataset, expand the values and load to data model (when you are in Query Editor go "Home" - "Close & Load", click the small arrow down and select . "Close & Load To..." and select "Only Create Connection" and check "Add this data to the Data Model")



                          Inside the "Power Pivot" tab you can add a new Measure to calculate the quartile for this table. I used



                          quartile:=PERCENTILE.EXC(AllData[AllValues];0,25)


                          as I dont think there is really special quartile function.






                          share|improve this answer














                          I dont think this is possible only using formulas. You can easily create formulas to have the separate lists to avoid problem of having more total lines than excel rows, but I dont know about any way to append these lists into one big list inside functions. MAybe someone else knows a way, but in the meantime there is another way in Excel:



                          What you can use is power query and power pivot. You start with the table that you have and add it as to Queries (Data - "Get & Transform Data" section - "From Table/Range". I have Excel 2016 so if you have a different version the path is most probably different or in older versions these tools are not available)



                          Now edit the query and add another column using List.Repeat to create your full dataset, expand the values and load to data model (when you are in Query Editor go "Home" - "Close & Load", click the small arrow down and select . "Close & Load To..." and select "Only Create Connection" and check "Add this data to the Data Model")



                          Inside the "Power Pivot" tab you can add a new Measure to calculate the quartile for this table. I used



                          quartile:=PERCENTILE.EXC(AllData[AllValues];0,25)


                          as I dont think there is really special quartile function.







                          share|improve this answer














                          share|improve this answer



                          share|improve this answer








                          edited Nov 12 '18 at 9:01

























                          answered Nov 12 '18 at 8:18









                          Pavel_V

                          1,0251814




                          1,0251814






























                              draft saved

                              draft discarded




















































                              Thanks for contributing an answer to Stack Overflow!


                              • Please be sure to answer the question. Provide details and share your research!

                              But avoid



                              • Asking for help, clarification, or responding to other answers.

                              • Making statements based on opinion; back them up with references or personal experience.


                              To learn more, see our tips on writing great answers.





                              Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                              Please pay close attention to the following guidance:


                              • Please be sure to answer the question. Provide details and share your research!

                              But avoid



                              • Asking for help, clarification, or responding to other answers.

                              • Making statements based on opinion; back them up with references or personal experience.


                              To learn more, see our tips on writing great answers.




                              draft saved


                              draft discarded














                              StackExchange.ready(
                              function () {
                              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53256089%2fexcel-calculate-quartile-and-median-to-generate-box-plot-given-a-list-of-value%23new-answer', 'question_page');
                              }
                              );

                              Post as a guest















                              Required, but never shown





















































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown

































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown







                              這個網誌中的熱門文章

                              Xamarin.form Move up view when keyboard appear

                              Post-Redirect-Get with Spring WebFlux and Thymeleaf

                              Anylogic : not able to use stopDelay()