4 Replies Latest reply: Nov 26, 2012 10:53 AM by Wayne Contello
dapacarter Level 1 Level 1 (0 points)

I have data in my numbers spread sheet

I want to create a chart of the data that is running from top to bottom of my sheet vertically

 

the demo shows me how to create charts vertically but I want to create a chart with all the infromation running down the sheet

 

as an example

 

I have 500 companies listed

 

company name of contact postiion country

 

I want to have a chart that give me % of clients in each country

 

is this possible?


MacBook Pro, iOS 6.0.1, numbers
  • 1. Re: numbers
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Dapa,

     

    Your question is one we see from time to time, in one form or another. It's a multi-step process to get to where you want to be. Here's how I would approach it, and of course there could be many variations:

    Screen Shot 2012-11-26 at 8.59.53 am.png

    We will add two auxiliary columns to your data table. Refer to my example. You will have to translate column names to match your situation and you will have to substitute the name of your data table for the name "Data" in the equations of your Countries Table from which the chart will be made.

     

    In Aux Column 1, Cell C2, write:

    =COUNTIF(B$1:B2, B)

     

    In Aux Column 2, Cell D2, write:

    =IF(C=1, B, "")

     

    Fill Down the formulas to the bottom of your Data table.

     

    The new table "Countries" starts out blank. Copy the Aux Column 2 of your Data Table and Edit > Paste Values into Column A of your Countries Table. Then, Sort Column A of the Countries table.

     

    In B2 of the Countries table, enter:

    =COUNTIF(Data :: B, A)

     

    Fill Down far enough to include all the countries in column A.

     

    Select the data in the Countries Table including the Header Row, then Insert > Chart > Pie.

     

    There are some options in the Table Inspector, such as "Show Series Name" that you will want to select.

     

    That's the basic approach.

     

    Jerry

  • 2. Re: numbers
    dapacarter Level 1 Level 1 (0 points)

    wow this is very complicated...

     

    i would have thought that apple would have come up with a simpler way to do this?

     

    to be honest I find it very hard to follow your instriuctions?

  • 3. Re: numbers
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Dapa,

     

    I apologize if the method I proposed seems daunting to you. It's the best I can do, both in terms of design and explanation.

     

    There are three equations,  one Copy/Paste-Values manual operation and a Sort. That's about as skinny as it can get.

     

    You can't chart what you haven't calculated. You haven't calculated the number of clients per company in your data list, so I gave you the table to do that. Then I charted the result of the client/country calculations.

     

    Maybe someone with better language skills will come along.

     

    Jerry

  • 4. Re: numbers
    Wayne Contello Level 6 Level 6 (13,620 points)

    Are you saying that you tried the method Jerry posted and it didn't work?  something was unclear?  Can you detail what you didn't understand?

     

    These small suggestons may help clarify details in Jerrys post:

    1) Edit > Paste Values

      this means select the menu item Paste Values from the menu "Edit".  it is short hand listd in the order you will actually see the items to select appear:

    Screen Shot 2012-11-26 at 12.46.41 PM.png

     

     

    2)

    In Aux Column 1, Cell C2, write:

    =COUNTIF(B$1:B2, B)

     

     

    this means in the table named "Date" cell C2 type everything on the next line.  In this case that is:

    =COUNTIF(B$1:B2, B)

     

    said another way, select everything from the equal sign to the close parenthesis, then copy, then select the cell C2 and paste.  The cell should and the formula bar should look like:

    Screen Shot 2012-11-26 at 12.52.44 PM.png


    Not sure what else to add until you post back specifics on what is difficult.

     

    Regards,

    Wayne