2 Replies Latest reply: Sep 7, 2010 4:37 AM by Jerrold Green1
paulclark Level 2 Level 2 (185 points)
I have a spreadsheet full of clients from different cities, and I'd like to create a chart (3D ideally) showing which cities have the most client and which have the least.

For example, 158 live in City A, 132 City B all the way down to 1 client in City V.

Currently my spreadsheet has first, last, street, ZIP, city, state, misc info A, misc info B, misc info C Etc Etc.

I may also like to create a chart showing totals of one of the Misc columns.

Mac Pro, Macbook Pro, iMac, Cube, 7300, Mac II, Mac OS X (10.6.4)
  • Barry Level 7 Level 7 (29,220 points)
    paulclark wrote:
    I have a spreadsheet full of clients from different cities, and I'd like to create a chart (3D ideally) showing which cities have the most clients and which have the fewest.

    For example, 158 live in City A, 132 City B all the way down to 1 client in City V.

    Currently my spreadsheet has first, last, street, ZIP, city, state, misc info A, misc info B, misc info C Etc Etc.

    I may also like to create a chart showing totals of one of the Misc columns.


    Use a subsidiary table to collect the names of the cities in your list, and to count them. Chart the results. Here's an example.

    Client Count

    Cities are listed in column B, and may be in any order. Spelling counts, as the formula in column E identifies each city by the exact contents of the cell.

    Column E contains a formula that counts the number of different entries in column B, and marks the row containing the first occurrence of each with a serial number.

    The formula in E2 is
    =IF(COUNTIF($B$1:B2,B)=1,MAX($E$1:E1)+1,"")
    Fill down to the end of the column.

    Column A in the second table (For Graph) contains a formula that constructs a reference to the cell containing the first occurrence of each City name on the Main table, and copies that name to it's cell.

    The formula in A2 is
    =IFERROR(OFFSET(Main :: $A$1,MATCH(ROW()-1,Main :: $E,0)-1,1),"")
    Fill down to the end of the column.

    Column B contains a formula that counts all occurrences in column B of the Main table of the name in its row in column A.

    The formula in B2 is
    =IF(COUNTIF(Main :: B,A2)>0,COUNTIF(Main :: B,A2),"")
    Fill down to the end of the column.

    Regards,
    Barry

    PS: Descriptions of each function used, and further examples of their use can be found in the iWork Formulas and Functions User Guide. You can download the guide (and the Numbers '09 User Guide) through the Help menu in Numbers.
    B
  • Jerrold Green1 Level 7 Level 7 (29,945 points)
    Hi Paul,

    I'll "second" the method Barry gave you for creating a summary table and chart. Further, I'll suggest that you consider just adding Shadow and perhaps some interesting color to your chart rather than going with a 3D Chart. Especially if you have a large amount of data, 3-D bogs you down and doesn't add much to the effect unless you have a presentation theme you are trying to match.

    Jerry