10 Replies Latest reply: Jan 28, 2013 12:42 PM by Badunit
Nutteel Level 1 Level 1 (0 points)

Brand 1.pngBrand 2.pngBrand 3.png

i use pop-up with every cell in column B. i want to do a chart from the column B only. as you can see if i can do the chart properly, the chart will be honda is almost 90% and BMW around 10 % but what i got is the chart include the column A in making a chart. How can i solve this problem? Thanks for every answers.

  • Badunit Level 6 Level 6 (11,380 points)

    You need to convert your list of automobile manufacturers into data for the chart.

     

    In another table (Table 2) that has a header column,

     

    1. List all the automobile companies in column A, the header column.
    2. In column B will be a formula like =COUNTIF(Table 1 :: B,A) . This will count how many times each company appears in your data-entry table (Table 1).
    3. Plot column B as a pie chart

     

    Your pie chart will include all the companies in the legend.  If you only want to include companies that have a count > 0 then,

     

    1. Open up the Reorganize panel
    2. Set up a filter for "show only rows where column B > 0. This will hide all the rows with zeros in them so they won't get plotted.
    3. You may have to select the chart and go to the chart inspector and uncheck "Show hidden data". By default it is not checked.

     

    Before:

    Screen Shot 2013-01-14 at 9.13.44 AM.png

     

    After:

     

    Screen Shot 2013-01-14 at 9.16.07 AM.png

  • Wayne Contello Level 6 Level 6 (14,940 points)

    I would make a seprate table that summarizes the manufacturer selections.  all graphs have at a minimum two axis... so you need to create a table with the axis.... in your case manufacturer and count of

     

    I suggestion a summary table where column A is the list of manufacturers (from column B) and the count of those manufacturers.  Something like:

    Screen Shot 2013-01-14 at 8.12.19 AM.png

     

    The table on the left is intended to be the same as the data tables you presented.  I title this table "Data"-- on the right.

     

    Make a new table called "Summary" and build as follows:

    1) the first row and column are headers

    2) column A contains the list of manufacturers

    3) B2=COUNTIF(Data :: B, "="&A2)

    this means that in cell B2 type "=COUNTIF(Data :: B, "="&A2)" without the double quotes

     

    now select B2 and fill down as needed

     

    4) select B2 to the end of the data, then select the pie chart from the charts menu in the tool bar

  • Nutteel Level 1 Level 1 (0 points)

    Thanks a lot,Badunit. You solved my problem half way already.

       -But frome the picture above, i have two columns of manufacturer. How can i apply your formula to this situation.

       -I also have Table 1, Table 2 and Table 3 with the same layout. It happens because i copy and paste Table 1 to new page. Should i expand Table 1 instead of copy and paste the table ?

      

    sorry to keep you with me a little longer.thanks again

  • Nutteel Level 1 Level 1 (0 points)

    Thanks a lot, Wayne Contello. i don't fully understand your formula because all i can do is copy and paste it. the program said there's something wrong with your answer and i can't explain it to you. I do really appreciate your answers to have more solution it's like you have more tools to deal with problems. thanks anyway.

  • Wayne Contello Level 6 Level 6 (14,940 points)

    If you can post a screen shot showing the problem I can help correct the problem.  the MOST likey cause is that the source table (the one on the left in my images) is not named the same as I named the table... I called it "Data".  You name tables by double clicking the name on the left panel, then typing the new name:

    Screen Shot 2013-01-15 at 6.02.22 PM.png

  • Badunit Level 6 Level 6 (11,380 points)

    To pick up both rows of manufacturers,

     

    B2=COUNTIF(Data :: B, "="&A2) + COUNTIF(Data :: G, "="&A2)

     

    It would be easier to expand Table 1 versus creating new tables that all need to be added up.  Each time you create a new table you'll have to modify the formula to include the new table

     

    B2=COUNTIF(Table 1::B, "="&A2) + COUNTIF(Table 1::G, "="&A2)

    will become

    B2=COUNTIF(Table 1::B, "="&A2) + COUNTIF(Table 1::G, "="&A2) + COUNTIF(Table 2::B, "="&A2) + COUNTIF(Table 2::G, "="&A2)

    and so on and so on. It'll get out of hand fast.

  • Nutteel Level 1 Level 1 (0 points)

    Thanks everyone.

  • Nutteel Level 1 Level 1 (0 points)

    Thanks everyone

  • Nutteel Level 1 Level 1 (0 points)

    Hello again. after i follow your advice everything went fine. But when i try to make a chart, i can't see the percentage of number 9 (which is appear as 0). I don't know why when the result equal 9 the formula shown it as 0. Can you help me just one more time. Thanks a lot.Chart.png

  • Badunit Level 6 Level 6 (11,380 points)

    If you are referring to the formula you have selected in your table, that answer is correct.  7425/9 = 825 with no remainder. MOD gives the remainder which is 0.